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/201...-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

and if so - are y'all hiring?

[calvinwings]

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

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