-
In support of this post https://excelfox.com/forum/showthrea...ge51#post12782
Security tweaks
Code:
# Will like XP or Win7 Disable Windows Defender Disable Defender Updates Set UAC to Never Prompt Disable Meltdown Flag Disable Windows Malware Scan
$securitylow.Add_Click({
Write-Host "Lowering UAC level..."
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" -Name "ConsentPromptBehaviorAdmin" -Type DWord -Value 0
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" -Name "PromptOnSecureDesktop" -Type DWord -Value 0
Write-Host "Disabling Windows Defender..."
If (!(Test-Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender")) {
New-Item -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender" -Force | Out-Null
}
Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender" -Name "DisableAntiSpyware" -Type DWord -Value 1
If ([System.Environment]::OSVersion.Version.Build -eq 14393) {
Remove-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" -Name "WindowsDefender" -ErrorAction SilentlyContinue
} ElseIf ([System.Environment]::OSVersion.Version.Build -ge 15063) {
Remove-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" -Name "SecurityHealth" -ErrorAction SilentlyContinue
}
Write-Host "Disabling Windows Defender Cloud..."
If (!(Test-Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet")) {
New-Item -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Force | Out-Null
}
Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Name "SpynetReporting" -Type DWord -Value 0
Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Name "SubmitSamplesConsent" -Type DWord -Value 2
Write-Host "Disabling Meltdown (CVE-2017-5754) compatibility flag..."
Remove-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\QualityCompat" -Name "cadca5fe-87d3-4b96-b7fb-a231484277cc" -ErrorAction SilentlyContinue
Write-Host "Disabling Malicious Software Removal Tool offering..."
If (!(Test-Path "HKLM:\SOFTWARE\Policies\Microsoft\MRT")) {
New-Item -Path "HKLM:\SOFTWARE\Policies\Microsoft\MRT" | Out-Null
}
Set-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\MRT" -Name "DontOfferThroughWUAU" -Type DWord -Value 1
$wshell.Popup("Operation Completed",0,"Done",0x0)
})
Code:
# Enable Windows Malware Scan Enable Meltdown Flag Disable Windows Defender Set UAC to Always Prompt Disable Defender Updates
$securityhigh.Add_Click({
Write-Host "Raising UAC level..."
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" -Name "ConsentPromptBehaviorAdmin" -Type DWord -Value 5
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System" -Name "PromptOnSecureDesktop" -Type DWord -Value 1
Write-Host "Disabling SMB 1.0 protocol..."
Set-SmbServerConfiguration -EnableSMB1Protocol $false -Force
Write-Host "Enabling Windows Defender..."
Remove-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender" -Name "DisableAntiSpyware" -ErrorAction SilentlyContinue
If ([System.Environment]::OSVersion.Version.Build -eq 14393) {
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" -Name "WindowsDefender" -Type ExpandString -Value "`"%ProgramFiles%\Windows Defender\MSASCuiL.exe`""
} ElseIf ([System.Environment]::OSVersion.Version.Build -ge 15063) {
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Run" -Name "SecurityHealth" -Type ExpandString -Value "`"%ProgramFiles%\Windows Defender\MSASCuiL.exe`""
}
Write-Host "Enabling Windows Defender Cloud..."
Remove-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Name "SpynetReporting" -ErrorAction SilentlyContinue
Remove-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\Windows Defender\Spynet" -Name "SubmitSamplesConsent" -ErrorAction SilentlyContinue
Write-Host "Disabling Windows Script Host..."
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows Script Host\Settings" -Name "Enabled" -Type DWord -Value 0
Write-Host "Enabling Meltdown (CVE-2017-5754) compatibility flag..."
If (!(Test-Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\QualityCompat")) {
New-Item -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\QualityCompat" | Out-Null
}
Set-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\QualityCompat" -Name "cadca5fe-87d3-4b96-b7fb-a231484277cc" -Type DWord -Value 0
Write-Host "Enabling Malicious Software Removal Tool offering..."
Remove-ItemProperty -Path "HKLM:\SOFTWARE\Policies\Microsoft\MRT" -Name "DontOfferThroughWUAU" -ErrorAction SilentlyContinue
$wshell.Popup("Operation Completed",0,"Done",0x0)
})
-
khskjhkshhhfhfhfslhafhalfh
-
test
Code:
https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page52
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs
HTML Code:
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs
PHP Code:
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs
Code:
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range(”A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occurred" }
finally { $excel .Quit() } # this section will be done if an error occurs
-
test
Code:
https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page52
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs
HTML Code:
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs
PHP Code:
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs
Code:
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range(”A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occurred" }
finally { $excel .Quit() } # this section will be done if an error occurs
-
test
Code:
https://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page52
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs
HTML Code:
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs
PHP Code:
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occured" } ; finally {$excel.Quit() } # this section will be done if an error occurs
Code:
Remove-Variable * -ErrorAction SilentlyContinue # This is very helpful when developing and debuging skript in the ISE, because the ISE has a habit of maintaining variables values between script executions, so this is needed, or else a removed variable may still be there - when fucking about with variables, this can get you in a very frustrating mess. In technical terms: By default variables are persistant. https://pscustomobject.github.io/powershell/howto/PowerShell-ISE-Clear-Variables/
# https://eileenslounge.com/viewtopic.php?t=33011&sid=726de7ffbd0c03680b62280fd86753e0
# Path I have used for my text file output 'C:\Users\Admin\Desktop\test.txt' here full line Get-Service|Select-Object name,displayname,starttype|Format-Table -AutoSize|Out-File -FilePath 'C:\Users\Admin\Desktop\test.txt' -Width 2000
# and for Excel example file C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls
try # Most of the main coding is in a try section ==============================================================================
{[object]$excel = New-Object -ComObject Excel.Application # https://www.youtube.com/watch?v=xc8A7Z0JLB0&t=995s
$excel.Visible = $true
[string]$excelPath = "C:\Users\Admin\Desktop\PowerShellAshishRajFeedback.xls"
$excelWB = $excel.workbooks.Open($excelPath)
# Worksheets info
$excelWS=$excelWB.WorkSheets.item("AshishRajCOM")
$excelWS.Activate() # It seems that in PowerShell script an extra () is needed
$excelWS.Cells.Item(1, 1).Value = "name" ; $excelWS.Cells.Item(1, 2).Value = "displayname" ; $excelWS.Cells.Item(1, 3).Value = "starttype"
# write in service
[int]$rowWS = 2
ForEach($Service in Get-Service) { $excelWS.Cells.Item($rowWS, 1).Value = $Service.Name.ToString() ; $excelWS.Cells.Item($rowWS, 2).Value = $Service.DisplayName.ToString() ; $excelWS.Cells.Item($rowWS, 3).Value = $Service.StartType.ToString()
If($Service.Status -eq "Running") { $excelWS.Cells.Item($rowWS, 1).Range(”A1:C1").Font.ColorIndex = 10 }
elseif($Service.Status -eq "Stopped") { $excelWS.Cells.Item($rowWS, 1).Range("A1:C1").Font.ColorIndex = 3 }
$rowWS++ }
$excelWS.Cells.Columns("A:C").EntireColumn.AutoFit() # Tidy up column widths
# $excelWB.SaveAs($excelPath)
$excelWB.Save() # It seems that in PowerShell script an extra () is needed
$excel.Quit() } # =============================================================================================================
catch { Write-Host "some error occurred" }
finally { $excel .Quit() } # this section will be done if an error occurs
-
<div class="gmail_chip gmail_drive_chip"> </div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/1UFQySI4QjTTV0O5xnLnLfx5iJh_qDzct/view?usp=drive_web" target="_blank" rel="noopener" aria-label="5.50 8.18 Clean Up Windows 10 _ 3 Steps For A Faster Computer-mWHiP9K8fQ0_16 10 2019.wmv"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" alt="" data-upload="true" /> <span dir="ltr">5.50 8.18 Clean Up Windows 10 _ 3 Steps For A ...</span></a><img src="res/6c654bc40d912309e7cc090257628b4f/texteditor/void.gif" alt="" data-upload="true" /></div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/11mjTzHbcdY1EwVaV0-AIAmYgC2DCsp7X/view?usp=drive_web" target="_blank" rel="noopener" aria-label="7.56 8 (Ransome) How to Make Windows 10 Secure-pGcerfVqYyU_31 01 2020.wmv"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" alt="" data-upload="true" /> <span dir="ltr">7.56 8 (Ransome) How to Make Windows 10 Secur...</span></a><img src="res/6c654bc40d912309e7cc090257628b4f/texteditor/void.gif" alt="" data-upload="true" /></div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/1b_akgpleMvL4lD4qXYffzUEXh8WhUyZ7/view?usp=drive_web" target="_blank" rel="noopener" aria-label="8.45 Speed Up Windows 10 in 2020-8E6OT_QcHaU_20 06 2020.wmv"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" alt="" data-upload="true" /> <span dir="ltr">8.45 Speed Up Windows 10 in 2020-8E6OT_QcHaU_20...</span></a><img src="res/6c654bc40d912309e7cc090257628b4f/texteditor/void.gif" alt="" data-upload="true" /></div>
<div class="gmail_chip gmail_drive_chip"> </div>
<div class="gmail_chip gmail_drive_chip"><a href="https://drive.google.com/file/d/1O63RdRCQS2OvWa50F56m6KyryVsBImIK/view?usp=drive_web" target="_blank" rel="noopener" aria-label="8.94 Creating New Windows 10 Debloat Scripts fo..."><img src="https://drive-thirdparty.googleusercontent.com/16/type/video/x-ms-wmv" alt="" data-upload="true" /> <span dir="ltr">8.94 Creating New Windows 10 Debloat Scripts fo...</span></a></div>
<div class="gmail_chip gmail_drive_chip"> </div>
-
Test Burgandy
Generic color name: Burgundy
Colors that make up #8C001A
RGB: 140, 0, 26 - HSL: 0.97, 1.00, 0.27
Purple[COLOR="#800080"]Purple[/COLOR]
Purple[COLOR=Purple]Purple[/COLOR]
Maroon[COLOR="#800000"]Maroon[/COLOR]
Maroon[COLOR=Maroon]Maroon[/COLOR]
Burgundy[COLOR="#8C001A"]Burgundy[/COLOR]
Windows10[color="#3E0000"]Windows10[/COLOR]
color:#A82D00 $NFS
HTML Code:
<div class=WordSection1>
<p class=MsoNormal><span lang=EN-GB style='font-size:9.0pt;line-height:115%;
font-family:"Lucida Console";mso-bidi-font-family:"Lucida Console";color:#A82D00;
mso-ansi-language:EN-GB;mso-fareast-language:DE'>$NFS</span><span lang=EN-GB
style='font-size:9.0pt;line-height:115%;font-family:"Lucida Console";
mso-bidi-font-family:"Lucida Console";mso-ansi-language:EN-GB;mso-fareast-language:
DE'><span style='mso-spacerun:yes'> </span><span
style='color:dimgray'>=</span> <span style='color:blue'>New-Object</span> <span
class=SpellE><span style='color:blueviolet'>system.Windows.Forms.Button</span></span></span><span
lang=EN-US style='mso-ansi-language:EN-US'><o:p></o:p></span></p>
</div>
Code:
'Sub Makro9BBBurgundy() ' ' https://excelfox.com/forum/showthread.php/2417-Test-BB-Code-Highlighting-and-Colors?p=16370#post16370
' With Selection
' .Font.Color = 1704076
' .Text = "[color=""#8C001A""] " & .Text & " [/color]"
' .Collapse Direction:=wdCollapseEnd
' End With
'End Sub
Sub Makro9BBBurgundy() ' Ctrl+Shift+B,G ' https://eileenslounge.com/viewtopic.php?f=26&t=37808&p=292620#p292620
Dim Text1 As String
Dim Text2 As String
Text1 = "[color=""#8C001A""] "
Text2 = " [/color]"
With Selection
.InsertBefore Text:=Text1
.InsertAfter Text:=Text2
ActiveDocument.Range(Selection.Start, Selection.Start + Len(Text1)).Font.Size = 8
ActiveDocument.Range(Selection.End - Len(Text2), Selection.End).Font.Size = 8
.Font.Color = 1704076
.Collapse Direction:=wdCollapseEnd
End With
End Sub
-
1 Attachment(s)
Hello Atlantis764
Welcome to ExcelFox
I don’t have any experience with UserForms
and
I don’t really understand the full picture of what you are trying to do.
Here is a screenshot of the file you uploaded: https://excelfox.com/forum/showthrea...ll=1#post16365
I can see that there is some correlation in the coloured cells, but all I can understand from your explanation is that you want to match Name and Project and Task as you showed in the workbook
The best I can do is that I can get you started on doing that matching:
We can make an array which has as many elements as there are data rows in Database1
Each element will contain a string made up from each row of the
Name & Project & Task
We can do something similar for worksheet Database
Then you can match the strings in the two arrays.
Run this demo macro and I think you will see what I mean
Code:
' https://excelfox.com/forum/showthread.php/2783-User-Form-entry-in-a-second-sheet-need-help-with-VBA-code?p=16371&viewfull=1#post16371
Sub MatchNameProjectTask()
Rem 0 Worksheets info
Dim WsD As Worksheet, WsD1 As Worksheet
Set WsD = ThisWorkbook.Worksheets("Database"): Set WsD1 = ThisWorkbook.Worksheets("Database1")
Dim LrD As Long, LrD1 As Long
Let LrD = WsD.Range("A" & WsD.Rows.Count & "").End(xlUp).Row: Let LrD1 = WsD1.Range("A" & WsD1.Rows.Count & "").End(xlUp).Row
Rem 2 make arrays of concatenated words
'Dim v: v = WsD.Evaluate("=D2:D4&E2:E4&F2:F4"): v = WsD.Evaluate("=D2:D" & LrD & "&E2:E" & LrD & "&F2:F" & LrD & "")
Dim arrD() As Variant: Let arrD() = WsD.Evaluate("=D2:D" & LrD & "&E2:E" & LrD & "&F2:F" & LrD & "")
Dim arrD1() As Variant: Let arrD1() = WsD1.Evaluate("=A2:A" & LrD1 & "&B2:B" & LrD1 & "&C2:C" & LrD1 & "")
Rem 3 compare arrays
Dim rwD As Long, rwD1 As Long
For rwD = 2 To LrD
For rwD1 = 2 To LrD1
If arrD(rwD - 1, 1) = arrD1(rwD1 - 1, 1) Then MsgBox prompt:="match for " & arrD(rwD - 1, 1) & " at Database row " & rwD & " Database1 row " & rwD1
Next rwD1
Next rwD
End Sub
Here's the same basic macro done slightly differently
Code:
Sub MatchNameProjectTask2()
Rem 0 Worksheets info
Dim WsD As Worksheet, WsD1 As Worksheet
Set WsD = ThisWorkbook.Worksheets("Database"): Set WsD1 = ThisWorkbook.Worksheets("Database1")
Dim LrD As Long, LrD1 As Long
Let LrD = WsD.Range("A" & WsD.Rows.Count & "").End(xlUp).Row: Let LrD1 = WsD1.Range("A" & WsD1.Rows.Count & "").End(xlUp).Row
Rem 2 make arrays of concatenated words
Dim arrD() As String, arrD1() As String
ReDim arrD(2 To LrD): ReDim arrD1(2 To LrD1)
Dim rwD As Long, rwD1 As Long
For rwD = 2 To LrD
Let arrD(rwD) = WsD.Range("D" & rwD & "") & WsD.Range("E" & rwD & "") & WsD.Range("F" & rwD & "")
Next rwD
For rwD1 = 2 To LrD1
Let arrD1(rwD1) = WsD1.Range("A" & rwD1 & "") & WsD1.Range("B" & rwD1 & "") & WsD1.Range("C" & rwD1 & "")
Next rwD1
Rem 3 compare arrays
For rwD = 2 To LrD
For rwD1 = 2 To LrD1
If arrD(rwD) = arrD1(rwD1) Then MsgBox prompt:="match for " & arrD(rwD) & " at Database row " & rwD & " Database1 row " & rwD1
Next rwD1
Next rwD
End Sub
Alan
-
1 Attachment(s)
Hi Kris
Just one very minor observation: You will need Excel 2010 or higher because I think .ForeColor.Brightness isn't supported in earlier Excel
Attachment 3832
(I checked in a few versions of 2010 and higher, and all seems OK)
Alan
-
2 Attachment(s)
Hi Liviu
It’s possibly difficult for me to understand the full picture as I know nothing about UserForms.
I doubt therefore that I will be able to give you exactly the final coding you need, since I don’t understand the workings of UserForms.
Also, if I click that Form button , then I see this, which is impossible for me to see anything in.
https://i.postimg.cc/8cdDypfn/Click-Form-Button.jpg
Attachment 3833
( If you have an issue / problem specific to something associated with the UserForm then I can’t help further. If for example, there is a simple typo in Task, or you have task rather than Task, then I can’t see that in that UserForm )
The best I can do therefore is give again some ideas that takes the matching further and goes on to match those date values, and then puts the Amount in the appropriate place in database1
(Basically, what I did for you before is going some way to demo to you coding that does that sort of row matching based on the various criteria of matching headings. So that was just matching the rows, which matched effectively "Name" and "Project" and the "Task).
This next macro is basically the same as my last. But instead of the demo message box, it now adds the "Amount" value to be added to Database1 sheet in the corresponding cell
The way I have done this extra section, is to convert the Year and Month from column B and C in Database, (along with an assumed day of the first of the month) to an Excel date serial number *****
I then compare that with the date serial numbers for the top row of Database1.
A match will then tell me which column to put the Amount in
Code:
Sub MatchNameProjectTask3()
Rem 0 Worksheets info
Dim WsD As Worksheet, WsD1 As Worksheet
Set WsD = ThisWorkbook.Worksheets("Database"): Set WsD1 = ThisWorkbook.Worksheets("Database1")
Dim LrD As Long, LrD1 As Long
Let LrD = WsD.Range("A" & WsD.Rows.Count & "").End(xlUp).Row: Let LrD1 = WsD1.Range("A" & WsD1.Rows.Count & "").End(xlUp).Row
Rem 2 make arrays of concatenated words
Dim arrD() As String, arrD1() As String
ReDim arrD(2 To LrD): ReDim arrD1(2 To LrD1)
Dim rwD As Long, rwD1 As Long
For rwD = 2 To LrD
Let arrD(rwD) = WsD.Range("D" & rwD & "") & WsD.Range("E" & rwD & "") & WsD.Range("F" & rwD & "")
Next rwD
For rwD1 = 2 To LrD1
Let arrD1(rwD1) = WsD1.Range("A" & rwD1 & "") & WsD1.Range("B" & rwD1 & "") & WsD1.Range("C" & rwD1 & "")
Next rwD1
'2b) Array of date serials from Database1
Dim arrDtSerials() As Variant, LcD1 As Long
Let LcD1 = WsD1.Cells(1, WsD1.Columns.Count).End(xlToLeft).Column
Let arrDtSerials() = WsD1.Range("A1").Resize(1, LcD1).Value2
Rem 3 compare arrays for headings
For rwD = 2 To LrD
For rwD1 = 2 To LrD1
If arrD(rwD) = arrD1(rwD1) Then ' MsgBox prompt:="match for " & arrD(rwD) & " at Database row " & rwD & " Database1 row " & rwD1
'3b We have a heading match , so now match the date
Dim DteSerial As Variant
' Let DteSerial = WsD.Evaluate("=DATEVALUE(""1 " & WsD.Range("C2").Value & " " & WsD.Range("B2").Value & """)")
Let DteSerial = WsD.Evaluate("=DATEVALUE(""1 " & WsD.Range("C" & rwD & "").Value & " " & WsD.Range("B" & rwD & "").Value & """)")
Dim MtchRes As Variant
Let MtchRes = Application.match(DteSerial, arrDtSerials(), 0)
If IsError(MtchRes) Then MsgBox prompt:="No date match": Exit Sub
Let WsD1.Cells(rwD1, MtchRes) = WsD.Range("G" & rwD & "").Value
Else
End If
Next rwD1
Next rwD
End Sub
So this would be the Before, as we had before: https://excelfox.com/forum/showthrea...ll=1#post16377
Then, after running that macro, this would be the After https://excelfox.com/forum/showthrea...ll=1#post16378
Alan
*****Unfortunately dates in Excel and VBA are a real pain in the arse, since different excel versions and land versions and user setting all give different results, so you may need to tweak some of the coding that gets the date match. There is no known way around these problems. It makes sharing files to different people with dates in them sometimes impossible.
The macro is working in my Excel
Its based on this bit working to give me the correct date serial number from worksheet Database
Code:
Sub Dts()
Rem 0 Worksheets info
Dim WsD As Worksheet
Set WsD = ThisWorkbook.Worksheets("Database")
Dim DteSerial As Variant
Let DteSerial = Evaluate("=DATEVALUE(""1-January-2022"")") ' 44562 : Variant/Double
Let DteSerial = WsD.Evaluate("=DATEVALUE(""1-"" & ""January"" & ""-2022"")") ' 44562 : Variant/Double
Let DteSerial = WsD.Evaluate("=DATEVALUE(""1-" & WsD.Range("C2").Value & "-2022"")") ' 44562 : Variant/Double
Let DteSerial = WsD.Evaluate("=DATEVALUE(""1-" & WsD.Range("C2").Value & "-2022"")") ' 44562 : Variant/Double
Let DteSerial = WsD.Evaluate("=DATEVALUE(""1-" & WsD.Range("C2").Value & "-" & WsD.Range("B2").Value & """)") ' 44562 : Variant/Double
Let DteSerial = WsD.Evaluate("=DATEVALUE(""1 " & WsD.Range("C2").Value & " " & WsD.Range("B2").Value & """)") ' 44562 : Variant/Double
End Sub
Ref http://www.eileenslounge.com/viewtop...290229#p290229
https://excelfox.com/forum/showthrea...otes-and-Tests
