CODEWITHSUNDEEP
excelvba
Allwyn P
Allwyn P

Reputation: 43

Changing function call from static to dynamic VBA

I have a function that I call multiple times, 14 times minimum, as of now I am statically calling each method and changing each of the variables that have to be entered in the function.

code for function call as of now.

If HeaderExists("W_Sheet", "BM") Then 'Checking if function needs to be called
 
    Set rng = ws.Rows(1).Find(What:="BM", LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    prevHeader = ws.ListObjects("W_Sheet").HeaderRowRange.Cells(1, rng.column - 1).Value 'find the previous header to the current one
    
    Call splitColumn(rng.column, "BM", "BM", prevHeader) ' actual calling of the function with parameter passing
  End If

Now this particular code will be repeated 14 times,(or as many number of headers are present) with only the parameters changing. Is there a way to introduce this in a loop of some kind??

EDIT Forgive me for not being Clear,

Points to note

  1. Am splitting my table based on the columns thus am checking if a particular header is present or not and based on that I am executing this code.

  2. please note the splitcolumn sub does creates new worksheets, and pastes the data there, to ensure sheets dont end up scrambled, I use prevheader to anchor new sheet to old sheet(checking if sheet by prevheader name exists and creating a new sheet on that)

the one scenario this dosent happen is the first sheet, I have hardcoded which sheet, the new one should be anchored to

  1. What I would like to happen is as follows

3.1) Code creates an array of all headers in the table

3.2) Code disregards the first 8 headers

3.3) Code passes the name of 9th header and the hardcoded sheet name to the function

3.4) from the 10th header onwards, it passes the name of each header(nth) and the previous(n-1)th header to the function.

is this possible to do??

Upvotes: 0

Views: 178

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Please, try the next code:

Sub testIteration()
    Dim ws As Worksheet, lastCol As Long, rngH As Range, arrH, El, count As Long
    
    Set ws = ActiveSheet 'use here the necessary sheet
    lastCol = ws.cells(1, ws.Columns.count).End(xlToLeft).column

    arrH = ws.Range(ws.cells(1, 9), ws.cells(1, lastCol)).Value
    For Each El In arrH
         count = count + 1
         If headerExists("W_Sheet", CStr(El)) Then 'Checking if function needs to be called
            Call SplitColumn(count + 8, El, El, IIf(count = 1, "Sheet1", arrH(1, count - 1))) ' actual calling of the function with parameter passing
        End If
    Next
End Sub

Upvotes: 1

Sam
Sam

Reputation: 5721

There are some details lacking, so some guesses below.
With a loop like this

    For Each ColName In Array("BM", "AB", "XY", "XZ")
        ' Your logic goes here
    Next

you can run it once per entry in the array. Put your code inside the for loop and replace every "BM" with ColName

Upvotes: 0

Related Questions