0

I am having trouble with something that I think should be quite simple and I can't figure out why it isn't giving me the result I want. I have an Excel worksheet sht_Attributes that is laid out as below:

enter image description here

I want to get the last (farthest right) cell in row 1 that has got anything in it. Beyond column G, there is no data in row 1. My code (below) seems to return $A$1, unless I specify a string or letter e.g. "Wedge" in which case it returns the correct cell:

'set header row as range var
Set headRow = sht_Attributes.Rows(1)
With headRow
    Set lastCol = .Find(What:="*", After:=.Cells(1, .Columns.Count - 1), SearchOrder:=xlPrevious, LookIn:=xlValues, LookAt:=xlPart)
End With

My understanding is as follows:

What:="*" means find any string of length 1 or more (no matter what characters)

LookAt:=xlPart means non-exact matches are included (I would say this is not necessary for a * wildcard?)

LookIn:=xlValues means just look for values - all of the strings in row 1 are values

After:=.Cells(1, .Columns.Count - 1) means start looking after the further-right-but-one cell in the row (in my version of Excel this is XFC1)

SearchOrder:=xlPrevious means search 'backwards' through the range

Any ideas? Thanks

jackelsey
  • 169
  • 1
  • 5
  • 17
  • You forgot `SearchOrder:=xlByColumns` – Scott Craner Nov 30 '17 at 15:32
  • You could also use `Set lastCol = Cells(1,Columns.Count).End(XlToLeft)`. – BruceWayne Nov 30 '17 at 15:33
  • 2
    ^^^^ but make sure to assign the parent sheet to the range object: `Set lastCol = sht_Attributes.Cells(1,sht_Attributes.Columns.Count).End(XlToLeft)` – Scott Craner Nov 30 '17 at 15:35
  • It turns out after a quick break and my code-blindness wearing off the problem was I was confusing `SearchOrder` with `SearchDirection`. It should have been `SearchDirection:=xlPrevious` – jackelsey Nov 30 '17 at 15:56

0 Answers0