0

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??

Improve this question
19
  • Your question is not so clear, at least, according to my way of understanding... Do you have list of headers to be checked? I suppose that HeaderExists function validates if "BM" exists as a header. What do you like doing, in fact? To parse the header list and the run the code for each of them? What "BM" is? Is it a header, or something else? Commented Aug 18, 2021 at 11:01
  • Could you share the code where you use your snippet at least 14 times and of course, the code of the 'function' which is just a Sub, not a Function since it doesn't return anything? Commented Aug 18, 2021 at 11:09
  • And what do you need changing each time? "BM" string? If not, what else? If "BM" can you supply a list of these 14 'things' to be automatically updated? Commented Aug 18, 2021 at 11:24
  • 1
    Pedantic point: If HeaderExists("W_Sheet", "BM") = True is equivalent to If HeaderExists("W_Sheet", "BM") -- there is no reason to compare (what seems to be) a Boolean with a Boolean. It already is a truth value so doesn't need a comparison to turn it into a truth value. Dropping the = True makes the code slightly more readable. Commented Aug 18, 2021 at 11:41
  • @FaneDuru, my apologies on not being clear on my requirements, I have edited the question please have a look, yes Parsing the headers and running code for each is the main idea. Commented Aug 18, 2021 at 12:39

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I am getting a Subscript out of range error, at the function call Call SplitColumn() more specifically , here ->arrH(1, I - 1) I is empty This is during the initial run of the code, E1 is still "BM", it hasent proceeded further
@Allwyn P It should be count -1. Please, try the updated code.
just a small edit its arrH(1,count). The code works wonderfully. Thanks for introducing me to CStr and IIF beautiful functions and works so well with my code.
@Allwyn P Glad I (finally) could help. But it looks I still did not understand your question completely... count -1 should return the previous array element, which in my understanding should be the previous header. In fact, I think I can understand now when writing... Your previous way was based on finding of the header, which was named as prevHeader. Not needing it, this prevHeader, which was not really a previous header, I didn't reflexively realize that it should be, in fact, the actual one.
:D Glad we cleared this out.. @FaneDuru, I have one more active question in Stack, could you please look at this? link
|
0

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

Improve this answer

1 Comment

Hi @Sam, thanks for the answer, please look at the edits in the question, is there a way to include all the conditions to your code???

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.