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
- Thread is marked as Resolved.
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
-
-
-
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!