help with defining column only from range of cells

[Guest]

In the following code the sales1fieldiamt is based on a specific cell.
I have it so the rows are incrementing the way I need but I have to
increment the column starting with the cell reference from the range as
shown below.
So instead of ("d" & iPivotRowSales) I need to have the column be the column
from
FirstCellRef (or Refa). (FirstCellRef=R5C40 or Refa=AN5) If someone could
help me with this that would be great.

Thanks

iPivotRowSales = 7

With Worksheets("Sales Summary").Range("D5:AV5")
Set PivotPeriod = .Find(2, LookIn:=xlValues)
If PivotPeriod <> Period Then
Do Until PivotPeriod = Period
FirstCellRef = PivotPeriod.Address(ReferenceStyle:=xlR1C1)
FirstCellRefa = PivotPeriod.Address
Set PivotPeriod = .FindNext(PivotPeriod)
Loop
iColumn = Val(Right(FirstCellRef, 2)) '
End If
End With
sales1fieldiamt = Worksheets("sales summary").Range("d" &
iPivotRowSales).Value

 

[Scott]

Could you go:

Dim MyColumn as Long

iPivotRowSales = 7

With Worksheets("Sales Summary").Range("D5:AV5")
Set PivotPeriod = .Find(2, LookIn:=xlValues)
If PivotPeriod <> Period Then
Do Until PivotPeriod = Period
FirstCellRef = PivotPeriod.Address(ReferenceStyle:=xlR1C1)
MyColumn = PivotPeriod.Column
FirstCellRefa = PivotPeriod.Address
Set PivotPeriod = .FindNext(PivotPeriod)
Loop
iColumn = Val(Right(FirstCellRef, 2)) '
End If
End With
sales1fieldiamt = Worksheets("sales
summary").Cells(iPivotRowSales,MyColumn).Value

Scott