• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Identifying the column name

Want to copy the entire column identifying the column header in a VBA code, in the below grid need to copy column with the header "current Month Balance"

and column may vary from sheet to sheet. Please help with the code snippet

BS/PLParticularsParticulars with divisionTypeBalanceBalanceOpening JVFinal op bal for Jan 19Current Month Balance
 
I take it you know how to copy a column from one place to another; your question is how to find the column first, right?

Some people may advise you to use the Find method. I've never been comfortable with that; I'd probably do it the manual way, like this:
Code:
Const rH = 1 'header row
Set owb = ThisWorkbook
Set ows = owb.Worksheets("your worksheet name")
For jc = 1 to 999 'or however many columns you think you may have to search
  If ows.Cells(rH, jc).Value = "current Month Balance" Then Exit For
  next jc
If jc > 999 then 'abend with a message that the header wasn't found
' Now jc is the number of the column you want.
 
No need to loop just using the VBA function Range.Find or with the worksheet function MATCH …​
Other efficient 'no loop' way is to use a named range or better an Excel Table.​
 
Yes, and my method (if you check to column 999) will probably take a noticeable length of time to run if the header isn't there. If the range (however you determine it) has more than a few dozen cells, better to read the the range into an array and check it there; that runs much faster.
 
hi

the below worked with faster processing with a minimal line of code

Cells.Find(What:="Current_Month_Balance", LookAt:=xlWhole).Select

Thanks
 
Last edited by a moderator:
I've had trouble with the Find method. I can't remember exactly what troubles I've had, because I haven't used it in quite a while, but somewhere in the back of my mind is a preference to avoid it. No doubt it would serve me better to learn how to use it, and to learn its vagaries (if indeed it has any) so that I don't have to keep on finding workarounds to it.
 
The usual issue of misusing Range.Find method is to not apply the correct date format or to not use the appropriate property …​
Next time you have an issue with the easy Range.Find method just create here a new tread with a workbook attached !​
 
Also, when using Range.Find.
If you use CTRL + F before the code is run. And if you've changed options in dialog...
Code will use options set during manual process, unless explicitly set in code.

So, it's safer to explicitly set these parameter arguments in the code.
 
Back
Top