Hi All,
I am struggling to find a way to use VBA to insert a formula into a current sheet (Paste) which references a second sheet (Control). Basically im an using an if formula to determine whether a cell equals the concatenation of two other cells in sheet 2:
it is that last line:
which is bugging out, not sure why my referencing is incorrect
PLease assist.
Thanks!
I am struggling to find a way to use VBA to insert a formula into a current sheet (Paste) which references a second sheet (Control). Basically im an using an if formula to determine whether a cell equals the concatenation of two other cells in sheet 2:
VBA Code:
Sub adding_months()
Dim lcol As Long
Dim cNext As Range
Dim artsd As Range, rng As String
Dim routing_column1 As Range
Dim Fnd As Range
Dim UsdRws As Long
Set cNext = Worksheets("paste").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
cNext.Resize(1, 3).Value = Array("Month", "Year", "Match")
Set cNext = Worksheets("paste").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
lcol = Worksheets("paste").Cells(1, Columns.Count).End(xlToLeft).Column
last_lin = Worksheets("paste").Cells(Rows.Count, 1).End(xlUp).Row
With ThisWorkbook.Sheets("Paste")
.AutoFilterMode = False
With .Range("A1:FF1")
Set rfind = .Find(What:="Month", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rfind Is Nothing Then
routing_column = Split(rfind.Address, "$")(1)
Debug.Print rfind
Debug.Print routing_column
End If
End With
rng = routing_column & "2:" & routing_column & last_lin
Debug.Print rng
With Sheets("Paste")
.AutoFilterMode = False
UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
Set Fnd = .Range("A1:FF1").Find("ART start date", , , xlWhole, , , False, , False)
'If Fnd Is Nothing Then
.Range((rng)).FormulaR1C1 = "=month(Rc" & Fnd.Column & ")"
With .Range("A1:FF1")
Set rfindy = .Find(What:="Year", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rfindy Is Nothing Then
routing_columny = Split(rfindy.Address, "$")(1)
Debug.Print rfindy
Debug.Print routing_columny
End If
End With
rngy = routing_columny & "2:" & routing_columny & last_lin
Debug.Print rngy
With Sheets("Paste")
.AutoFilterMode = False
UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
Set Fndy = .Range("A1:FF1").Find("ART start date", , , xlWhole, , , False, , False)
'If Fnd Is Nothing Then
' Exit Sub
' Else
.Range((rngy)).FormulaR1C1 = "=year(Rc" & Fndy.Column & ")"
' End If
With .Range("A1:FF1")
Set rfindm = .Find(What:="Match", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rfindm Is Nothing Then
routing_columnm = Split(rfindm.Address, "$")(1)
End If
End With
rngm = routing_columnm & "2:" & routing_columnm & last_lin
Debug.Print rngm
.Range((rngm)).FormulaR1C1 = "=concatenate(Rc" & rfind.Column & ",Rc" & rfindy.Column & ")"
With .Range("A1:FF1")
Set rfindf = .Find(What:="Filter", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rfindf Is Nothing Then
routing_columnf = Split(rfindf.Address, "$")(1)
End If
End With
rngf = routing_columnf & "2:" & routing_columnf & last_lin
Debug.Print rngf
Dim ws2 As Worksheet
Set ws2 = ThisWorkbook.Worksheets("Control")
.Range((rngf)).FormulaR1C1 = "=if((Rc" & rfindm.Column & ")=(CONCATENATE(ws2.range($L$3).value,ws2.range($M$3).value),"""",""No"")"
End With
End With
End With
End Sub
it is that last line:
VBA Code:
.Range((rngf)).FormulaR1C1 = "=if((Rc" & rfindm.Column & ")=(CONCATENATE(ws2.range($L$3).value,ws2.range($M$3).value),"""",""No"")"
which is bugging out, not sure why my referencing is incorrect
PLease assist.
Thanks!