Macro to apply formulas to columns

[SeanMorrowJ]

Hello, so I'd need a macro that applies the following formulas to certain columns:

(the formulas refer to specific cells and when I use the fill down button they work just fine obviously, but in a Macro it might be better to use RC??)

Formula 1: (Remove initial 4 characters of the cell)
Starts at: J2
Apply to Columns: J, Q, X, AE, AL, AS, AZ, BG, BN, BU, CB, CI, CP, CW, DD, DK, DR, DY, EF, EM, ET, FA, FH, FO, FV, GC, GJ, GX, HE, HL, HS
The headers of these columns always contains the word "TYPE"

Formula 2:

=IF(L2="";"";IF(P2<>"";(LEN(TRIM(P2))-LEN(SUBSTITUTE(TRIM(P2);",";""))+1);" "))

Starts at: O2
Apply to Columns: O, V, AC, AJ, AQ, (+7), (+7), ... , HX
The headers of these columns always contains the word "CLASS"

Formula 3:

=IF(L2="YES"; (143,7)+((O2-1)*96,48); IF(L2="YES +25";(179,63)+((O2-1)*120,59); IF(L2="YES +50"; (215,55)+((O2-1)*144,71);" ")))

Starts at: M2
Apply to Columns: M, T, AA, (+7), (+7), ... , HV
The headers of these columns always contains the word "FEE"

I've been trying for hours to get one to work, but I get lost when "translating" Excel formulas to VBA...

Thanks so much for any help provided.

Hahah for Formula 1 I had this, which I'm posting just for laughs:

Sub TYPE()    For Each cell In Range("J1", Range("J65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("Q1", Range("Q65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("X1", Range("X65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("AE1", Range("AE65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("AL1", Range("AL65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("AS1", Range("AS65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("AZ1", Range("AZ65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("BG1", Range("BG65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("BN1", Range("BN65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("BU1", Range("BU65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("CB1", Range("CB65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("CI1", Range("CI65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("CP1", Range("CP65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("CW1", Range("CW65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("DD1", Range("DD65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("DK1", Range("DK65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("DR1", Range("DR65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("DY1", Range("DY65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("EF1", Range("EF65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("EM1", Range("EM65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("ET1", Range("ET65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("FA1", Range("FA65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("FH1", Range("FH65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("FO1", Range("FO65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("FV1", Range("FV65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("GC1", Range("GC65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("GJ1", Range("GJ65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("GX1", Range("GX65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("HE1", Range("HE65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("HL1", Range("HL65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
    For Each cell In Range("HS1", Range("HS65536").End(xlUp))
    If Not IsEmpty(cell) Then
    cell.Value = Right(cell, Len(cell) - 3)
    End If
    Next cell
End Sub

 

[Fennek]

Hello,

just an idea to shorten your code:

dim lr as long
sub Test()
lr = cells(rows.count, 1).end(xlup).row
for j = 1 to cells(1, columns.count).end(xltoleft).column
    if instr(1, cells(1,j), "Type") > 0 then Type(j)
    'if instr(1, cells(1,j), "Class") > 0 then Class(j)
    'if instr(1, cells(1,j), "Fee") > 0 then Fee(j)
next j
end sub
sub Type(byval Sp as integer)
    for i = 2 to lr
    with cells(i,SP)
    if len(.value) > 3 then .value = right(.value, len(.value)-3)
    end with
    next i 
end sub

It is untested! You have to add for #2 and #3 the code.

regards

 

[SeanMorrowJ]

Thanks fennek! But I'm afraid I have no clue what that code is saying hah '

Also, I believe one can't just copy paste the formulas from Excel to VBA, as they need to be adapted in a certain way??

 

[SeanMorrowJ]

I've been trying but I can't make sense of it . My superlong macro for Formula 1 works, thought it doesn't seem like the cleanest way, but it's fine cause it gets the work done. I might just use that approach, but I'm having trouble adapting Formulas 2 & 3 into VBA. Could anyone help with this?? How should those Formulas be written in VBA?? Thanks in advance!

 

[SeanMorrowJ]

Never mind, I found a work around using Autofilldown. Thanks!