Help with VBA code please

[senpai]

Dear All,

I need help with VBA code. So following is what i want to achieve.

1. I have data in column A starting from A2. The number of row changes everyday. Suppose, on Monday there were 5000 row, on Tuesday it can be either 5500 or 4327. So its not fixed.
2. Column B has formula in B2. Formula is ="T"&A2
3. The code should extend formula in column B till the last row corresponding to last row of column A.

For example, if last row in A is 2000 then B2="T"&A2000, if last row in A is 5000 then B5000="T"&A5000

 

[Alex Blakenburg]

If you convert your Data into an Excel Table, you don't need VBA to do what you are trying to do.
Steps:

• Select any cell in your data range
• Either Insert > Table (3rd button from the left
  OR
  Ctrl + T
• Click on the tab Table Design and in the white box on the far left give the table a meaningful name
  (I like to have a prefix such as tbl_ so that later when I can't remember the name I just need to type in tbl and I get all my tables)
• Ideally change your formula to use Table Structured referencing by in row A column B type ="T"& then click on the cell in column A,

If you start with Column B empty the formula will automatically populate all the way down and will continue to do so whenever you add new rows.
If B is not empty, copy it down the first time and as above it will continue to automatically populate in the future

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help with vba code please - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[senpai]

Hi Alex,
Thanks for the quick response.
Making table is not feasible. Let me explain in more detail what I am doing. I am copying an excel sheet from different workbook to macro and renaming the copied sheet automatically to Sheet2. Then copying different columns from "Sheet2" and pasting it in different columns in Sheet1. After this is executed I want to achieve what i asked in question. Here is my complete code:

Sub OPOR()
Dim lRow As String
        MsgBox "Select OPOR vs Price Book file"
        fName = Application.GetOpenFilename(Title:="Select OPOR vs Price Book File", Filefilter:="Excel Files(*.xls*),*xls*")
        If fName <> False Then
        Set wb = Workbooks.Open(fName)
        End If
        Sheets("Sheet1").Copy After:=Workbooks("macro.xlsm").Sheets(Workbooks("macro.xlsm").Sheets.Count)
        ActiveSheet.Name = "Sheet" & Sheets.Count
        Sheets("Sheet2").Range("A4:A6500").Copy Destination:=Sheets("Sheet1").Range("A5")
        Sheets("Sheet2").Range("D4:F6500").Copy Destination:=Sheets("Sheet1").Range("B5")
        Sheets("Sheet2").Range("H4:I6500").Copy Destination:=Sheets("Sheet1").Range("E5")
        Sheets("Sheet2").Range("J4:J6500").Copy Destination:=Sheets("Sheet1").Range("H5")
        Sheets("Sheet2").Range("M4:S6500").Copy Destination:=Sheets("Sheet1").Range("J5")
        Sheets("Sheet2").Range("V4:V6500").Copy Destination:=Sheets("Sheet1").Range("Q5")
        Sheets("Sheet2").Range("Y4:Y6500").Copy Destination:=Sheets("Sheet1").Range("R5")
        Sheets("Sheet2").Range("AW4:AW6500").Copy Destination:=Sheets("Sheet1").Range("AA5")
        Sheets("Sheet2").Range("BA4:BB6500").Copy Destination:=Sheets("Sheet1").Range("AG5")
        Sheets("Sheet2").Range("BD4:BD6500").Copy Destination:=Sheets("Sheet1").Range("AI5")
        Sheets("Sheet1").Select

        Range("G5").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=""T""&RC[-1]"
        
        Range("F5").Select
        Selection.End(xlDown).Select
        Range("lrow").Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.FillDown
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            wb.Close False
        
End Sub

I tried to execute it with following code but it didn't work:

 Range("G5").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=""T""&RC[-1]"
        
        Range("F5").Select
        Selection.End(xlDown).Select
        Range("lrow").Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.FillDown
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

 

[senpai]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Help with vba code please - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sure Rory
I don't see the option to edit my post now Is there any way i can do it now?

 

[RoryA]

I've added the link in my post - unless there are more, in which case add them in a new post.

 

[Alex Blakenburg]

I tried to execute it with following code but it didn't work:

Range("G5").Select
        Application.CutCopyMode = False
        ActiveCell.FormulaR1C1 = "=""T""&RC[-1]"
       
        Range("F5").Select
        Selection.End(xlDown).Select
        Range("lrow").Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.FillDown
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

Can you confirm that you are wanting a formula in column G that fills down ="T"&F5 and then converts column G from being a formula to a hardcoded value ?

 

[Alex Blakenburg]

Can you confirm that you are wanting a formula in column G that fills down ="T"&F5 and then converts column G from being a formula to a hardcoded value ?

I have assumed what I asked you to confirm as being the case, so you want to replace the code you referred to in:-

I tried to execute it with following code but it didn't work:

(from Range("G5").Select TO Selection.PasteSpecial )

with the below and let me know if it works for you.

    Dim rng As Range
    Dim lastRow As Long
   
    lastRow = Range("F5").End(xlDown).Row
       
    Set rng = Range("G5:G" & lastRow)
   
    rng.Formula = "=" & """" & "T" & """" & " & " & Range("F5").Address(False, False)
    rng.Value = rng.Value