Macro with different cell ranges everyday

[cdalgorta]

Hi. Probably a macro beginners question. Apologies and thanks in advance.
So everyday I download "report A", the cells range are different because things get bought/sold. Ex: A3:L900 and the next day is A3:L1000.
*Simple example:
Day 1: Macro starts recording on a sheet with data range A3:L900
- I insert a new column B
- I type a function in cell B4[=IFERROR(LEFT(A4,FIND("_",A4)-1),A4)] and click on the corner of the cell B4 so it copies the function for all the rest of the rows in column B until B900
- Stop recording

Day 2: I run the same Macro on a sheet with data range A3:L1000:
-I run the macro and it only copies the function for cells B4:B900 and not for the rest B901:B1000

Question: What should I have done so the function I recorded on Day 1, also worked on B901:B1000 on Day 2? And hopefully also work on B901:B100000 in Day 500 in the future?
Hope I didn't confuse anyone and thanks in advance once again.

 

[mumps]

Use a variable to hold the number of the last row:

Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range ("A3:L" & LastRow)

 

[cdalgorta]

Use a variable to hold the number of the last row:

Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range ("A3:L" & LastRow)

Hi, apologies for the late response. Family emergency
Back to your response. I'm extremely new to VBA. I tried playing around with what you said and watched some videos, but could not make it work. Could you please tell me what exactly would I have to add/delete in the uploaded "VBA example" to make the "test sample" work even if it had more than 15 rows? Thank you very much in advance!

VBA:
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range ("A3:L" & LastRow)

Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Invoice"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IFERROR(LEFT(RC[-1],FIND(""_"",RC[-1])-1),RC[-1])"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A12")
ActiveCell.Range("A1:A12").Select
End Sub

Test Sample with 15 rows.xlsx
	A	B	C
1
2	Hello
3	invoice	cost	revenue
4	123	12	16
5	125	12	16
6	124_1	13	17
7	124_2	15	19
8	127	19	23
9	128	17	21
10	124_3	16	20
11	128	24	28
12	124_7	12	16
13	127	25	29
14	124_5	58	62
15	125	45	49
16
Hello

 

[mumps]

Start with your sheet looking like this:

Book1
	A	B	C
1	invoice	cost	revenue
2	123	12	16
3	125	12	16
4	124_1	13	17
5	124_2	15	19
6	127	19	23
7	128	17	21
8	124_3	16	20
9	128	24	28
10	124_7	12	16
11	127	25	29
12	124_5	58	62
13	125	45	49
Sheet1

Then try this macro:

Sub cdalgorta()
    Application.ScreenUpdating = False
    Dim v1 As Variant, v2 As Variant, i As Long, j As Long
    v1 = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    ReDim v2(1 To UBound(v1), 1 To 1)
    For i = 1 To UBound(v1)
        If InStr(v1(i, 1), "_") > 0 Then
            j = j + 1
            v2(j, 1) = Split(v1(i, 1), "_")(0)
        Else
            j = j + 1
            v2(j, 1) = v1(i, 1)
        End If
    Next i
    Range("A2").Resize(UBound(v2)) = v2
End Sub

There is no longer any need for a formula.

 

[cdalgorta]

Start with your sheet looking like this:

Book1
	A	B	C
1	invoice	cost	revenue
2	123	12	16
3	125	12	16
4	124_1	13	17
5	124_2	15	19
6	127	19	23
7	128	17	21
8	124_3	16	20
9	128	24	28
10	124_7	12	16
11	127	25	29
12	124_5	58	62
13	125	45	49
Sheet1

Then try this macro:

Sub cdalgorta()
    Application.ScreenUpdating = False
    Dim v1 As Variant, v2 As Variant, i As Long, j As Long
    v1 = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
    ReDim v2(1 To UBound(v1), 1 To 1)
    For i = 1 To UBound(v1)
        If InStr(v1(i, 1), "_") > 0 Then
            j = j + 1
            v2(j, 1) = Split(v1(i, 1), "_")(0)
        Else
            j = j + 1
            v2(j, 1) = v1(i, 1)
        End If
    Next i
    Range("A2").Resize(UBound(v2)) = v2
End Sub

There is no longer any need for a formula.

Thank you! The thing is that I would need the way to do it with the macro on my example, so I can use it as reference. Because that was just a sample. In the real thing for work, I need to use dozens of different formulas for hundreds of columns and thousands of rows. The above would only help me with 1 of the formulas, but not with the rest.
If you could please help me to know what I have to add/delete in the sample I gave, it would be perfect. Thank you again

VBA:
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range ("A3:C" & LastRow)

Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Invoice"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IFERROR(LEFT(RC[-1],FIND(""_"",RC[-1])-1),RC[-1])"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A12")
ActiveCell.Range("A1:A12").Select
End Sub

 

[mumps]

It is hard to suggest a solution without seeing your actual file. How many formulae are you using? Does each formula perform the same function as the one you posted? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet (de-sensitized if necessary). Alternately, you could upload a copy of your file which includes all the formulae, to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data. I wouldn't need the entire sheet, only a dozen or so rows of data should be enough.

 

[Alex Blakenburg]

See if this helps:
This is making as few changes as possible to what you had.
Normally, you would go through and remove all occurences of "Select" and "Activate". They are unnecessary and they make the code slow and hard to follow.

Sub FillDownFormula()

    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    'XXX Range ("A3:C" & LastRow)   ' XXX errors out
   
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Invoice"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(LEFT(RC[-1],FIND(""_"",RC[-1])-1),RC[-1])"
    ActiveCell.Select
    ' XXX Change this next line
    'Selection.AutoFill Destination:=ActiveCell.Range("A1:A12")
    ' XXX To this
    Selection.AutoFill Destination:=Range(ActiveCell, Range("B" & LastRow))
   
    ' XXX Remove this
    'ActiveCell.Range("A1:A12").Select
End Sub

 

[cdalgorta]

See if this helps:
This is making as few changes as possible to what you had.
Normally, you would go through and remove all occurences of "Select" and "Activate". They are unnecessary and they make the code slow and hard to follow.

Sub FillDownFormula()

    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    'XXX Range ("A3:C" & LastRow)   ' XXX errors out
  
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Offset(2, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Invoice"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(LEFT(RC[-1],FIND(""_"",RC[-1])-1),RC[-1])"
    ActiveCell.Select
    ' XXX Change this next line
    'Selection.AutoFill Destination:=ActiveCell.Range("A1:A12")
    ' XXX To this
    Selection.AutoFill Destination:=Range(ActiveCell, Range("B" & LastRow))
  
    ' XXX Remove this
    'ActiveCell.Range("A1:A12").Select
End Sub

omg! This worked perfectly. Thank you so much!!!!!

 

[Alex Blakenburg]

Glad we could help. Thanks for the feedback.
Come back to us when you ready to get rid of the "Selects"