Shg,
I tried importing your code and using it, and I am still coming up with problems about the code going to the next column. It will adjust all of the data in the first column, but does not change the values in the subsequent columns. All help is greatly appreciated!!!!
Mattie
---------- Post added at 08:01 AM ---------- Previous post was at 07:59 AM ----------
David,
Attached are my functions, ConvertToLetter() and matchEff2()
Set wks = Workbooks("UH-60M Forecast.xlsm").Worksheets("Pos 01M")
With Workbooks("UH-60M Hours.xlsx").Worksheets("UH-60M Pivot")
If Range("L5").Value <> .Cells(matchAOS(Range("B5")), matchEff2(wks.Range("L1"))) Then
For j = 12 To 35
For i = startRow - 1 To lastRow - 1
Cells(i, j).Formula = "=" & .Cells(i + 1, matchEff2(Cells(1, j))).Address(External:=True)
Next i
Next j
End If
End With
Public Function matchEff2(effectivity As Integer) As Integer
'Enter effectivity to determine which column that effectivity is in the hours spreadsheet
Dim searchRange As Range
Dim findColumn As Range
'Determines which column that effectivity is in the buyoff hours pivot
Workbooks("UH-60M Hours.xlsx").Worksheets("UH-60M Pivot").Activate
Set searchRange = Workbooks("UH-60M Hours.xlsx").Worksheets("UH-60M Pivot").Range("A4:Z4")
Set findColumn = searchRange.Find(effectivity, LookIn:=xlValues, Lookat:=xlWhole)
matchEff2 = findColumn.Column
End Function
Bookmarks