VBA to add column letter into code

Status
Not open for further replies.
M

Muktar888

New Member
Joined
Apr 30, 2017
Messages
17
Hi guys,

So I think I may be doing something technically wrong.

The code finds the column letter based on a string ("ART start date"), then I am trying to use the column letter in a range to calculate a formula. but it ends up printing the name of the range ("routing_column1 " in the formula instead of using it:

VBA Code: 
Sub adding_months()


Dim routing_column1 As Range

last_lin = Worksheets("paste").Cells(Rows.Count, 1).End(xlUp).Row

With ThisWorkbook.Sheets("Paste")
        .AutoFilterMode = False
  

With .Range("A1:FF1")
 Set rfind1 = .Find(What:="ART start date", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
 If Not rfind1 Is Nothing Then


routing_column1 = Split(rfind1.Address, "$")(1) & "2"


Range(("D2:D" & last_lin)) = "=Month(routing_column1)"

    
End If
End With
End With

End Sub

Overall im trying to find the column ART start date, then calculate the month in every row and print the month name in the last column.

Please assist, thanks.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
How about
VBA Code: 
Sub Muktar()
   Dim Fnd As Range
   Dim UsdRws As Long
   
   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 Exit Sub
      .Range("D2:D" & UsdRws).FormulaR1C1 = "=month(Rc" & Fnd.Column & ")"
   End With
End Sub
 
Upvote 0
Fluff said:
How about
VBA Code: 
Sub Muktar()
   Dim Fnd As Range
   Dim UsdRws As Long
 
   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 Exit Sub
      .Range("D2:D" & UsdRws).FormulaR1C1 = "=month(Rc" & Fnd.Column & ")"
   End With
End Sub
Click to expand...
Thanks Fluff
Hi Fluff,

This actually worked for me! thanks so much! It solved, although i have another question on a similar note, I will open a new post if need be? Just let me know.

so i have used the above technique to fill in two columns, one containing the month number and the other the year number. ie

.Range((rng)).FormulaR1C1 = "=month(Rc" & Fnd.Column & ")" &

.Range((rngy)).FormulaR1C1 = "=year(Rc" & Fndy.Column & ")"

I am now trying to concatenate this into a third column using:

VBA Code: 
.Range((rngm)).FormulaR1C1 = "=concatenate((Rc" & rngy.Column & "&" & rngy.Row & "),(Rc" & rngy.Column & "&" & rngy.Row & "))"

but there seems to be an error. "rngy" is the range column for the year and "rng" is the column for the month? not sure why it doesnt work?

in the immediate window debug.print of rngy and rng are: CB2:CB12690 & CA2:CA12690... not sure where I am going wrong.

THanks Muktar
 
Upvote 0
Can you post the code you are using.
 
Upvote 0
Fluff said:
Can you post the code you are using.
Click to expand...

Sure here it is in totality, i have incoporated your bit at the bottom:

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" & rngy.Column & "&" & rngy.Row & "),(Rc" & rngy.Column & "&" & rngy.Row & "))"


End With
End With
End With
End Sub
 
Upvote 0
Try
VBA Code: 
.Range((rngm)).FormulaR1C1 = "=concatenate(Rc" & rfind.Column & ",Rc" & rfindy.Column & ")"
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
I have created a separate post which think you would be able to answer in a second...

I am struggling to find a way to use the 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 ?
 
Upvote 0
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
You may continue this in your other thread.
 
Upvote 0
Status
Not open for further replies.

Similar threads

Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
344
Agnarr
Agnarr
S
  • Solved
Check for a specific month in a column
Replies
6
Views
185
Jeffrey Mahoney
Jeffrey Mahoney
V
  • Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
61
VanGirl
V
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
397
Legacy 352679
L
F
  • Question
VBA run code based off cell value
Replies
1
Views
318
igold
igold

Forum statistics

Threads
1,214,859
Messages
6,121,963
Members
449,059
Latest member
oculus

Share this page

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