I am new to VBA and cannot get this to work. I have an Office 2007 workbook which will start out with a worksheets in it, named "ChGridR1" then add a worksheet named "ChGridR2". I am trying to get the macro to detect the name and then do a find and replace with information from a third sheet "CHQ". I have tried If, Then, Else and Select Case but I cannot get either to work. I would be grateful if someone would tell me why. Thank you in advance for any help you can give.




Sub rhgridreplace()
'
' rhgridreplace Macro

Select Case Grid

Case Is = ("CHGridR1")

Sheets("CHGridR1").Select
Columns("J:J").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!B12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("L:L").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!D12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("M:M").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!F12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("C:C").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!B12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("E:E").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!D12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("F:F").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!F12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Case Is = ("CHGridR2")

Sheets("CHGridR2").Select
Columns("J:J").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!B12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("L:L").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!D12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("M:M").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!F12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("C:C").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!B12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("E:E").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!D12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("F:F").Select
Selection.Replace What:="#REF!", Replacement:="CHQ!F12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Select


End Sub