Write to a variable cell location

[GrantG3SA]

Hi,

Can someone advise me how to find the Column location from my first match so that I can make the range variable. My code below successfully finds the store in range C3 to H3. I want to know if its column E, then my range in bold when I copy must be E6 to E58. I cannot find how to make that range vary on the column number.

Sub SAVE()
Dim Store As String

'Set Store to Cell C2 from worksheet Entry
Store = Range("C2").Value
'Look for Store in Worksheet Orders to obtain range to copy to
If Not IsError(Application.Match(Store, Sheets("Orders").Range("C3:H3"), 0)) Then
'The value found in the given range
'Copy C3:C55 from Entry worksheet into found Range from Orders Worksheet
Worksheets("Orders").Range("C6:C58").Value = Worksheets("Entry").Range("C3:C55").Value


End If

End Sub

 

[Fluff]

Assuming the C2 cell is on the "Entry" sheet, try

Sub GrantG3SA()
    Dim Fnd As Range
    With Sheets("Entry")
        Set Fnd = Sheets("Orders").Range("C3:H3").Find(.Range("C2").Value, , , xlWhole, , , False, , False)
        If Not Fnd Is Nothing Then
            Fnd.Offset(3).Resize(53).Value = .Range("C3:C55").Value
        End If
    End With
End Sub

 

[GrantG3SA]

Assuming the C2 cell is on the "Entry" sheet, try

Sub GrantG3SA()
    Dim Fnd As Range
    With Sheets("Entry")
        Set Fnd = Sheets("Orders").Range("C3:H3").Find(.Range("C2").Value, , , xlWhole, , , False, , False)
        If Not Fnd Is Nothing Then
            Fnd.Offset(3).Resize(53).Value = .Range("C3:C55").Value
        End If
    End With
End Sub

Thanks Fluff. Works perfectly!

 

[Fluff]

You're welcome & thanks for the feedback