VBA formula to use different sheet

Muktar888

New Member
Joined
Apr 30, 2017
Messages
17
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:

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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
.FormulaR1C1 = "=if((Rc" & rfindm.Column & ")=(CONCATENATE(ws2.range($L$3).value,ws2.range($M$3)

You are setting it to be a formula with reference R1C1 but you are setting references of type A1

I checked your macro and observed the operation.
The macro can look like this:
VBA Code:
Sub adding_months_1()
  Dim sh As Worksheet, f As Range
  Dim lc As Long, lr As Long, col As Long, col2 As Long
  
  Set sh = Sheets("Paste")
  sh.AutoFilterMode = False
  lc = sh.Cells(1, Columns.Count).End(1).Column
  lr = sh.Range("A" & Rows.Count).End(3).Row
  sh.Cells(1, lc + 1).Resize(1, 3).Value = Array("Month", "Year", "Match")
  Set f = sh.Rows(1).Find("ART start date", , xlValues, xlWhole)
  If Not f Is Nothing Then
    col = f.Column
    sh.Range(sh.Cells(2, lc + 1), sh.Cells(lr, lc + 1)).FormulaR1C1 = "=MONTH(RC" & col & ")"
    sh.Range(sh.Cells(2, lc + 2), sh.Cells(lr, lc + 2)).FormulaR1C1 = "=YEAR(RC" & col & ")"
    sh.Range(sh.Cells(2, lc + 3), sh.Cells(lr, lc + 3)).FormulaR1C1 = "=RC[-2]&RC[-1]"
    Set f = sh.Rows(1).Find("Filter", , xlValues, xlWhole)
    If Not f Is Nothing Then
      col2 = f.Column
      sh.Range(sh.Cells(2, col2), sh.Cells(lr, col2)).FormulaR1C1 = _
        "=IF(RC" & lc + 3 & "=Control!R3C12&Control!R3C13,""Yes"",""No"")"
    End If
  End If
End Sub

But if the month, year and match columns are only temporary, then the month and year comparison can be direct with the following:

VBA Code:
Sub adding_months_2()
  Dim sh As Worksheet, f As Range, lr As Long, col As Long
  
  Set sh = Sheets("Paste")
  sh.AutoFilterMode = False
  lr = sh.Range("A" & Rows.Count).End(3).Row
  Set f = sh.Rows(1).Find("ART start date", , xlValues, xlWhole)
  If Not f Is Nothing Then
    col = f.Column
    Set f = sh.Rows(1).Find("Filter", , xlValues, xlWhole)
    If Not f Is Nothing Then
      With sh.Range(sh.Cells(2, f.Column), sh.Cells(lr, f.Column))
        .FormulaR1C1 = "=IF(MONTH(RC" & col & ")" & "&YEAR(RC" & col & ")=Control!R3C12&Control!R3C13,""Yes"",""No"")"
      End With
    End If
  End If
End Sub
 
Upvote 0
You are setting it to be a formula with reference R1C1 but you are setting references of type A1

I checked your macro and observed the operation.
The macro can look like this:
VBA Code:
Sub adding_months_1()
  Dim sh As Worksheet, f As Range
  Dim lc As Long, lr As Long, col As Long, col2 As Long
 
  Set sh = Sheets("Paste")
  sh.AutoFilterMode = False
  lc = sh.Cells(1, Columns.Count).End(1).Column
  lr = sh.Range("A" & Rows.Count).End(3).Row
  sh.Cells(1, lc + 1).Resize(1, 3).Value = Array("Month", "Year", "Match")
  Set f = sh.Rows(1).Find("ART start date", , xlValues, xlWhole)
  If Not f Is Nothing Then
    col = f.Column
    sh.Range(sh.Cells(2, lc + 1), sh.Cells(lr, lc + 1)).FormulaR1C1 = "=MONTH(RC" & col & ")"
    sh.Range(sh.Cells(2, lc + 2), sh.Cells(lr, lc + 2)).FormulaR1C1 = "=YEAR(RC" & col & ")"
    sh.Range(sh.Cells(2, lc + 3), sh.Cells(lr, lc + 3)).FormulaR1C1 = "=RC[-2]&RC[-1]"
    Set f = sh.Rows(1).Find("Filter", , xlValues, xlWhole)
    If Not f Is Nothing Then
      col2 = f.Column
      sh.Range(sh.Cells(2, col2), sh.Cells(lr, col2)).FormulaR1C1 = _
        "=IF(RC" & lc + 3 & "=Control!R3C12&Control!R3C13,""Yes"",""No"")"
    End If
  End If
End Sub

But if the month, year and match columns are only temporary, then the month and year comparison can be direct with the following:

VBA Code:
Sub adding_months_2()
  Dim sh As Worksheet, f As Range, lr As Long, col As Long
 
  Set sh = Sheets("Paste")
  sh.AutoFilterMode = False
  lr = sh.Range("A" & Rows.Count).End(3).Row
  Set f = sh.Rows(1).Find("ART start date", , xlValues, xlWhole)
  If Not f Is Nothing Then
    col = f.Column
    Set f = sh.Rows(1).Find("Filter", , xlValues, xlWhole)
    If Not f Is Nothing Then
      With sh.Range(sh.Cells(2, f.Column), sh.Cells(lr, f.Column))
        .FormulaR1C1 = "=IF(MONTH(RC" & col & ")" & "&YEAR(RC" & col & ")=Control!R3C12&Control!R3C13,""Yes"",""No"")"
      End With
    End If
  End If
End Sub

All i can say is wow for code optimisation lol... from soooo many lines to just a handful!!! Very impressed because i didnt even have to say what the purpose of my code is. I will be using your code in replacement of mine :) THanks Dante!!! How do i upvote your skill :D
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top