That's what I thought after posting. Just trying to get the formula to work properly.
Reference to a column depends on the column heading
-
julian_t -
November 10, 2021 at 9:46 AM -
Thread is marked as Resolved.
-
-
-
I have everything working, but I cannot get the formula to enter correctly at the moment.
-
I am sorry it is proving so difficult!
In some ways I am pleased - it just shows that I would never have figured it out!
But I apologise for not explaining it properly at the outset.
-
This code is finding the correct column and the date to look for in the data.
I'm getting an error in the formula but I can't see why. I'll take another look in the morning, hopefully it will be clearer then
Code
Display MoreSub AddFormula() Dim lRow As Long ''///iCol is the Column with Perios, iX is the period number from master workbook Dim iCol As Integer, iX As Integer ''///dte is th string to match in the formula Dim dte As String iX = ThisWorkbook.Sheets("Sheet1").Range("H17").Value dte = iX & " " & Choose(iX, "April", "May", "June", "July", "August", "September", "October", "November", "December", "January", "February", "March") With ActiveSheet lRow = .Range("A1").CurrentRegion.Rows.Count ''///find relevant column iCol = 8 - Cells.Find(What:="Posting Period", After:=Range("A2"), LookIn:=xlFormulas2 _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column ''"=IF(RC[-7]=""8 November"",RC[-1],RC[-2])" .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=" & dte & ",RC[-1],RC[-2])" End With End Sub
-
It seems to not like the dte part of the formula for some reason?
Leaving your code as is, but replacing the "dte" with a hard coded text, and it works. I did the same with "iCol" - but it is the "dte" that it doesnt like, which is rather mystifying
-
-
That's what I thought. I'm busy at the moment but I'll take a look later. I suspect it's probably missing "".
-
I have played around with quotes, brackets, spaces etc and cannot get this to work.
However the attached code does work - looks a bit clumsy, but it works! So it uses 90% of your code, so thank you very much.
Code
Display MoreSub AddFormula() Dim lRow As Long ''///iCol is the Column with Perios, iX is the period number from master workbook Dim iCol As Integer, iX As Integer ''///dte is th string to match in the formula Dim dte As String iX = ThisWorkbook.Sheets("Sheet1").Range("H17").Value dte = iX & " " & Choose(iX, "April", "May", "June", "July", "August", "September", "October", "November", "December", "January", "February", "March") Range("i2") = dte With ActiveSheet lRow = .Range("A1").CurrentRegion.Rows.Count ''///find relevant column iCol = 8 - Cells.Find(What:="Posting Period", After:=Range("A2"), LookIn:=xlFormulas2 _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Column If dte = "1 April" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""1 April"",RC[-1],RC[-2])" Else If dte = "2 May" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""2 May"",RC[-1],RC[-2])" Else If dte = "3 June" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""3 June"",RC[-1],RC[-2])" Else If dte = "4 July" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""4 July"",RC[-1],RC[-2])" Else If dte = "5 August" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""5 August"",RC[-1],RC[-2])" Else If dte = "6 September" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""6 September"",RC[-1],RC[-2])" Else If dte = "7 October" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""7 October"",RC[-1],RC[-2])" Else If dte = "8 November" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""8 November"",RC[-1],RC[-2])" Else If dte = "9 December" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""9 December"",RC[-1],RC[-2])" Else If dte = "10 January" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""10 January"",RC[-1],RC[-2])" Else If dte = "11 February" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""11 February"",RC[-1],RC[-2])" Else If dte = "12 March" Then .Range(.Cells(2, 8), .Cells(lRow, 8)).FormulaR1C1 = "=IF(RC[-" & iCol & "]=""12 March"",RC[-1],RC[-2])" End If End If End If End If End If End If End If End If End If End If End If End If End With End Sub
-
\i'll check it out later, I had a really busy day yesterday.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!