0

I copied this code from this post : stack post

Sub FindColumn()
  Dim rngAddress As Range
  Set rngAddress = Range("A1:BZ1").Find("STVCNTY CODE Mailing")
  If rngAddress Is Nothing Then
    MsgBox "STVCNTY CODE Mailing column was not found."
    Exit Sub
  End If
  Range(rngAddress, rngAddress.End(xlDown)).Select
End Sub

I seems to work except for one problem, it stops the 'select' when it encounters a empty cell. see pic stops select short

how can modify this code to select the entire column and change format to 000?

I have this done already thinking the column I was looking for was static but now I'm told the column position will vary by report i.e it be in column AN on Mondays report , be in column BA on tuesday, etc.

here is the simple code I already had:

' formats column to 000

Columns("AN:AN").Select

Selection.NumberFormat = "000"

Any help would be apprecaited

Community
  • 1
  • 1
rommel109g
  • 195
  • 1
  • 1
  • 6

1 Answers1

0

Try this. Go the bottom of the column and work up (no need to Select either).

Sub FindColumn()

Dim rngAddress As Range

Set rngAddress = Range("A1:BZ1").Find("STVCNTY CODE Mailing")
If rngAddress Is Nothing Then
  MsgBox "STVCNTY CODE Mailing column was not found."
  Exit Sub
End If

'just used range
Range(rngAddress, Cells(Rows.Count, rngAddress.Column).End(xlUp)).NumberFormat = "000"

'OR entire column
rngAddress.EntireColumn.NumberFormat = "000"

Range(rngAddress.Offset(1), rngAddress.Offset(199)).Formula = "=IFERROR(VLOOKUP(" & rngAddress.Offset(1, -1).Address(0, 0) & ",[CountyCodes2.xlsm]Sheet1!CountyTable,2,FALSE),"""")"

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • your code seems to work. Do I have to use one or the other of the last lines of your code? or can both be used together? – rommel109g Jan 04 '18 at 22:37
  • 2nd question if I may. I had another line in the code that performed a VLOOKUP based on where that header was: ' performs VLOOKUP Range("AN2:AN200").Formula = "=IFERROR(VLOOKUP(AM2,[CountyCodes2.xlsm]Sheet1!CountyTable,2,FALSE),"""")" how do I modify the Range to use the column that your code found the header ? The AM2 is supposed to be next to the column that was being searched for previously. wish I could attach pic in these comments to show you but I cant – rommel109g Jan 04 '18 at 22:54
  • I think the first is probably preferable as otherwise you are formatting all million cells which is probably unnecessary. Re the lookup I've added a line to the code which I think does what you want (assuming it should refer to the column to the left of rngAddress. – SJR Jan 04 '18 at 23:20