Compare Worksheets Using Macro 1

[Marryp]

I have several worksheets to compare and the result of the comparison will be in master worksheet. To check for the right worksheet to use the column Q must not be blank or null.

I have this code but there seems to be error on the quotes

Sub ValidateData()

Dim i As Integer
Dim intRowCount As Integer
Dim strFileNAme As String

    intRowCount = Application.Inputbox("Please enter the last row to validate")
    
    For i = 2 To intRowCount
    ActiveSheet.Cells(i, 1).Formula = "=IF(Elaine!Q2="Elaine",Elaine!A"&i&",(IF(Carla!Q2="Carla",Carla!A"&i&",(IF(Susanne!Q2="Susanne",Susanne!A"&i&",(IF(Michele!Q2="Michele",Michele!A"&i&",(IF(Faye!Q2="Faye",Faye!A"&i&",(IF(Sherry!Q2="Sherry",Sherry!A"&i&",(IF(Cathy!Q2="Cathy",Cathy!A"&i&",(IF(Pound!Q2="Pound",Pound!A"&i&","No value")))))))))))))))"
    ActiveSheet.Cells(i, 2).Formula = "=IF(Elaine!Q3="Elaine",Elaine!B"&i&",(IF(Carla!Q3="Carla",Carla!B"&i&",(IF(Susanne!Q3="Susanne",Susanne!B"&i&",(IF(Michele!Q3="Michele",Michele!B"&i&",(IF(Faye!Q3="Faye",Faye!B"&i&",(IF(Sherry!Q3="Sherry",Sherry!B"&i&",(IF(Cathy!Q3="Cathy",Cathy!B"&i&",(IF(Pound!Q3="Pound",Pound!B"&i&","No value")))))))))))))))"
    ActiveSheet.Cells(i, 3).Formula = "=IF(Elaine!Q4="Elaine",Elaine!C"&i&",(IF(Carla!Q4="Carla",Carla!C"&i&",(IF(Susanne!Q4="Susanne",Susanne!C"&i&",(IF(Michele!Q4="Michele",Michele!C"&i&",(IF(Faye!Q4="Faye",Faye!C"&i&",(IF(Sherry!Q4="Sherry",Sherry!C"&i&",(IF(Cathy!Q4="Cathy",Cathy!C"&i&",(IF(Pound!Q4="Pound",Pound!C"&i&","No value")))))))))))))))"
    ActiveSheet.Cells(i, 4).Formula = "=IF(Elaine!Q5="Elaine",Elaine!D"&i&",(IF(Carla!Q5="Carla",Carla!D"&i&",(IF(Susanne!Q5="Susanne",Susanne!D"&i&",(IF(Michele!Q5="Michele",Michele!D"&i&",(IF(Faye!Q5="Faye",Faye!D"&i&",(IF(Sherry!Q5="Sherry",Sherry!D"&i&",(IF(Cathy!Q5="Cathy",Cathy!D"&i&",(IF(Pound!Q5="Pound",Pound!D"&i&","No value")))))))))))))))"
    ActiveSheet.Cells(i, 5).Formula = "=IF(Elaine!Q6="Elaine",Elaine!E"&i&",(IF(Carla!Q6="Carla",Carla!E"&i&",(IF(Susanne!Q6="Susanne",Susanne!E"&i&",(IF(Michele!Q6="Michele",Michele!E"&i&",(IF(Faye!Q6="Faye",Faye!E"&i&",(IF(Sherry!Q6="Sherry",Sherry!E"&i&",(IF(Cathy!Q6="Cathy",Cathy!E"&i&",(IF(Pound!Q6="Pound",Pound!E"&i&","No value")))))))))))))))"
    Next i
    
End Sub

 

[Marryp]

This is a correct formula but needed to be translated into to a VBA code as there will be more or less 4000+ cells to be populated:

=IF(Elaine!Q2="Elaine",Elaine!A2,(IF(Carla!Q2="Carla",Carla!A2,(IF(Susanne!Q2="Susanne",Susanne!A2,(IF(Michele!Q2="Michele",Michele!A2,(IF(Faye!Q2="Faye",Faye!A2,(IF(Sherry!Q2="Sherry",Sherry!A2,(IF(Cathy!Q2="Cathy",Cathy!A2,(IF(Pound!Q2="Pound",Pound!A2,"No value")))))))))))))))

 

[PHV]

.Formula = "=IF(Elaine!Q2=""Elaine"",Elaine!A2,(IF(Carla!Q2=""Carla"",Carla!A2,(IF(Susanne!Q2=""Susanne"",Susanne!A2,(IF(Michele!Q2=""Michele"",Michele!A2,(IF(Faye!Q2=""Faye"",Faye!A2,(IF(Sherry!Q2=""Sherry"",Sherry!A2,(IF(Cathy!Q2=""Cathy"",Cathy!A2,(IF(Pound!Q2=""Pound"",Pound!A2,""No value"")))))))))))))))"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Marryp]

How do I concatenate a variable within the formula string? I am getting errors....

ActiveSheet.Cells(i, 1).Formula = "=IF(Elaine!Q2="Elaine",Elaine!A"&i&",(IF(Carla!Q2="Carla",Carla!A"&i&",(IF(Susanne!Q2="Susanne",Susanne!A"&i&",(IF(Michele!Q2="Michele",Michele!A"&i&",(IF(Faye!Q2="Faye",Faye!A"&i&",(IF(Sherry!Q2="Sherry",Sherry!A"&i&",(IF(Cathy!Q2="Cathy",Cathy!A"&i&",(IF(Pound!Q2="Pound",Pound!A"&i&","No value")))))))))))))))

 

[Marryp]

I tried this code using 2 double qoutes as suggested by PHV but I still get an error in the formula line. Compile error: Expected: end of statement

Sub ValidateData()

Dim i As Integer
Dim intRowCount As Integer
Dim strFileNAme As String

    intRowCount = Application.Inputbox("Please enter the last row to validate")
    
    For i = 2 To intRowCount
    ActiveSheet.Cells(i, 1).Formula = "=IF(Elaine!Q2=""Elaine"",Elaine!A"&i&",(IF(Carla!Q2=""Carla"",Carla!A"&i&",(IF(Susanne!Q2=""Susanne"",Susanne!A"&i&",(IF(Michele!Q2=""Michele"",Michele!A"&i&",(IF(Faye!Q2=""Faye"",Faye!A"&i&",(IF(Sherry!Q2=""Sherry"",Sherry!A"&i&",(IF(Cathy!Q2=""Cathy"",Cathy!A"&i&",(IF(Pound!Q2=""Pound"",Pound!A"&i&",""No value"")))))))))))))))"
    Next i
    
End Sub

 

[Marryp]

It is working now. I just have to put space between the variable and the & sign. I thought it will pick up the reserved symbol and variable.

Thanks for your help PHV!