Sleeper: VBA Macro help needed to compare to column different sheet

[calvinwings]

I need a help in completing a automated script for one of the application I am not able to complete the vba script which need to compare from sheet1 to sheet2 and update the sheet3 and auto save it

I am trying to get the column output in variable and later update sheet3 but not able to get the output

for example

sheet 1 has more than 200 servers

For example
DHHENSDVE31\TEST_BE



in sheet 2 i have column C , D and E , C has the server name and D has the variable i am trying to match to get the column E output
C D E


DHHENSDVE31\TEST_BE
Errorlog
Compliant



I have also attached the sheet


Sub Healthcheck()
' Select cell A2, *first line of data*.
Dim servername As String 'Instance name
Dim xStr As String 'trial and error
Dim Machinename As String 'Server name
Dim Ipaddress As String
Dim Healthcheck As String ' HC Value
Dim Status As String ' HC Status
Dim Username As String ' Issue User
'Sheets("Sheet1").Activate
Worksheets("ServerList").Activate
Range("A2").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
' Step down 1 row from present location.
xStr = ActiveCell.value
'MsgBox "Server is : " & xStr 'check for output
MsgBox "Server is : " & xStr
Healthcheck = "Errorlog"
Status = Index(Sheets("CSVReport").Range("E2:E10000"), Match(1, WorksheetFunction.CountIfs(Sheets("CSVReport").Range("C2:C10000"), xStr, Sheets("CSVReport").Range("D2:D10000"), Healthcheck), 0))
MsgBox "Server count is : " & Status
'If Not IsError(Application.Match(1, WorksheetFunction.CountIfs(Sheets("CSVReport").Range("C2:C10000"), xStr, Sheets("CSVReport").Range("D2:D10000"), Healthcheck), 0)) Then
'Status = Application.Match(1, WorksheetFunction.CountIfs(Sheets("CSVReport").Range("C2:C10000"), xStr, Sheets("CSVReport").Range("D2:D10000"), Healthcheck), 0)
'MsgBox "Server is : " & Status
'Else
MsgBox " no match is found in range"
'End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

[MINCUS1308]

First off, I really hope that's dummy data you've posted

Second, let me see if I understand the problem correctly:
If the Server Name appears on the worksheet ServerList
And on the CSVReport
Then you want move some data?

OR

If the Server Name appears on both lists
Then check the value in column D (Healthcheck) on the CSCReport Worksheet
Then update Column E (Status) on the CSCReport Worksheet

[calvinwings]

Hello, thank you for your reply

Yes I am trying to look for servername existing in sheet1 to servername and Healthcheck column (which is pass it has a string) CSVReport (Sheet2) and update the sheet3 and auto save the sheet3 per server

I want to loop through all the server existing in sheet1 , my problem is I don't want to use the Vlookup since i might need column either in left or right value

I am trying to see if there is anyway I can use FindIfs,Match,Index function to match to column C and D and get the column E has an output in VBA or if you have any idea please let me know

I want trying to use something like this in Macros

https://www.get-digital-help.com/2012/05/22/lookup-multiple-values-in-different-columns-and-return-a-single-value/

Please let me know Thanks in advance

[MINCUS1308]

Still have a little confusion as to what you are trying to do, but I think this is a good start:

This subroutine will step down column A in sheet1 (until it finds an empty cell)
And it will check to see if the EXACT same value appears in column C on sheet2
For every value searched - a message box will pop up telling you if it was found or not.


Sub Test()
i = 2
Do Until Sheet1.Cells(i, "A").Value = ""
With Sheet2.Columns("C")
Set C = .Find(Sheet1.Cells(i, "A").Value, After:=.Range("A1"), LookIn:=xlValues, lOOKAT:=xlWhole, SEARCHORDER:=xlByRows, SEARCHDIRECTION:=xlNext, MatchCase:=False)
If Not C Is Nothing Then
'Found
MsgBox "I Found: " & Sheet1.Cells(i, "A").Value
Else
'NotFound
MsgBox "I Could Not Find: " & Sheet1.Cells(i, "A").Value
End If
End With
i = i + 1
Loop
End Sub

If you can explain the next step a little better I can see what I can do.

[MINCUS1308]

So if I find a matching server name on sheet2 - I need to check the corresponding value in column D (on sheet2)?
What am I looking for in Column D? "ErrorLog"? or a different value? or just any value?

Also column C on sheet2 has duplicate values...
if I find a matching server name in column c but the wrong value in column D do I need to continue searching for other instances of the server name?

[calvinwings]

I am trying to search for every servername in sheet1 for example DEHENSVE31\TEST_BE
in sheet2 in column C to match the servername DEHENSVE31\TEST_BE and a string variable which i pass from the query Dim String as string String = "Errorlog" to match the column D these two values should match both in C column and D column and give the output of E Column as the server status hope you understand what i am trying to achieve

ie For example

Column C Column D Column E


DEHENSVE31\TEST_BE
Errorlog
Compliant



I think the script you posted check the C column

[MINCUS1308]

Sub Test()
HealthCheckCode = "Errorlog"
i = 2
Do Until IsEmpty(Sheet1.Cells(i, "A").Value)
ServerStatus = ""
ServerName = Sheet1.Cells(i, "A").Value

With Sheet2.Columns("C")
Set C = .Find(ServerName, LookIn:=xlValues, lOOKAT:=xlWhole, SEARCHORDER:=xlByRows, SEARCHDIRECTION:=xlNext, MatchCase:=False)
If Not C Is Nothing Then
If C.Offset(0, 1).Value = HealthCheckCode Then
ServerStatus = C.Offset(0, 2).Value
End If
FirstInstance = C.Address
Do
Set C = .FindNext(C)
If C.Offset(0, 1).Value = HealthCheckCode Then
ServerStatus = C.Offset(0, 2).Value
End If
Loop While FirstInstance <> C.Address
MsgBox "Server: " & ServerName & vbNewLine & "Health Check: " & HealthCheckCode & vbNewLine & "Sever Status: " & ServerStatus, vbOKOnly, "Server Details"
Else
MsgBox "I Could Not Find The Server: " & ServerName, vbCritical, "Server Not Found"
End If
End With
i = i + 1
Loop
End Sub

where does the output need to go on sheet3?

[calvinwings]

Yes I need to update sheet 3 , can you give the insight of the script I am new to macros VBA so that I can learn :)

Thank you very much for the script it works !!!

[MINCUS1308]

**Update no major changes

Sub Testing()
HealthCheckCode = "Errorlog"
i = 2
Do
ServerStatus = ""
ServerName = Sheet1.Cells(i, "A").Value
With Sheet2.Columns("C")
Set C = .Find(ServerName, LookIn:=xlValues, lOOKAT:=xlWhole, SEARCHORDER:=xlByRows, SEARCHDIRECTION:=xlNext, MatchCase:=False)
If Not C Is Nothing Then
If C.Offset(0, 1).Value = HealthCheckCode Then ServerStatus = C.Offset(0, 2).Value
FirstInstance = C.Address
Do
Set C = .FindNext(C)
If C.Offset(0, 1).Value = HealthCheckCode Then ServerStatus = C.Offset(0, 2).Value
Loop While FirstInstance <> C.Address
If Not IsEmpty(ServerName) Then MsgBox "Server: " & ServerName & vbNewLine & "Health Check: " & HealthCheckCode & vbNewLine & "Server Status: " & ServerStatus, vbOKOnly, "Server Details"

If ServerStatus <> "" Then
'ADD TO LOCATION ON SHEET3
'CODE GOES HERE
'Sheet3.Cells(,).Value = ...?
End If
Else
If Not IsEmpty(ServerName) Then MsgBox "I Could Not Find The Server: " & ServerName, vbCritical, "Server Not Found"
End If
End With
i = i + 1
Loop Until IsEmpty(ServerName)
End Sub

[MINCUS1308]

It looks like you need to create a version of sheet3 for every match found?
And update the data on the sheet with the servers information?

is that what you need to do next?

what should happen if I cannot find a matching server?

[MINCUS1308]

HAHAHAHA your username is ADMINIBM?
please tell me you are not an IBM Admin:rotlaugh:

and if so - are y'all hiring?

[calvinwings]

HAHAHAHA your username is ADMINIBM?
please tell me you are not an IBM Admin:rotlaugh:

and if so - are y'all hiring?


I am leaving the company since they don't have no new clients any more

I am MS SQL DBA though not into Vba but yea learning into :)

I could not delete the sheet after I uploaded I did a mistake

[calvinwings]

yea I need to update the sheet3 with all the input from sheet2 and auto save it for each server

these are the criteria which will fill the sheet3



Errorlog


AuditLogins


Password Requirements





for F5 requirement



Enforce Password Policy


MSSQL Logins, sa


MSSQL Logins, sa


Audit Login Attempts


Error Log Iterations




and update
F13 column in sheet3 also the date Field in F11 and F10 which is the Email ID

Also these columns need to be updated



H3


H3A


H3B




If i have any doubt in the script I will reach you :)

[calvinwings]

Sub ValuesOnly()
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Activate
'Copying a worksheet from ThisWorkbook into newly creadted workbook in the above statement
ThisWorkbook.Sheets("Sheet3").Copy Before:=wb.Sheets(1)
wb.SaveAs "C:\temp\test3.xlsx"
'"C:\Scripts" & Servername & ".xlsx"
End Sub

What is my mistake please let me know