Find Date variable within a row and return Column VBA

Question

I am looking to find a variable within a row and return the column reference. The code I have written so far is;

Dim VarianceDate As String
VarianceDate = Sheets("Summary").Range("C12").Value


Rows("6").Find(What:=VarianceDate, After:=ActiveCell, LookIn:=xlFormulas _
      , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
      MatchCase:=False, SearchFormat:=False).Activate

TargetCol = ActiveCell.Column

In this example the Variance date is 01/06/2015, however when I stepinto the code in VBA it returns nothing. Except When I search for it manually It finds the correct cell.

Eventually I would like to use the TargetCol reference to help me extract the correct data into another workbook.

Any help would be much appreicated.

Thanks

You're saying that VarianceDate = nothing? Could you try a line like: Debug.Print VarianceDate to confirm this? If so, you'd need to make sure that your sheet and range references are correct. — TMH8885
– TMH8885, Commented Jul 2, 2015 at 14:03
Nope, the variancedate = 01/06/2015 I think the issue lies within the "Find" part of the code. As this returns nothing i.e. it does not carry out any action. — Sean Bailey
– Sean Bailey, Commented Jul 2, 2015 at 15:06

Tragamor · Accepted Answer · 2015-07-02 17:06:05Z

1

You need to mention that the search term is a date and also if the activecell is not in your find range, it will throw an error due to the inclusion of After:=ActiveCell

Try the following code

Sub FindDateCol()

    Dim VarianceDate As String: VarianceDate = Sheets("Summary").Range("C12").Value

    Dim TargetCell As Range, TargetCol As Integer
    Set TargetCell = Rows("6").Find(What:=CDate(VarianceDate), LookIn:=xlFormulas, LookAt:=xlPart)
    If Not TargetCell Is Nothing Then TargetCol = TargetCell.Column

    MsgBox TargetCol

End Sub

edited Jul 2, 2015 at 17:06

answered Jul 2, 2015 at 15:14

Tragamor

3,6893 gold badges18 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ARich Over a year ago

This code won't return the desired result since the ActiveCell will currently be whichever cell was selected before the code ran. As a general rule, I'd stay away from using ActiveCell. Instead just say Msgbox TargetCell.Column.

Tragamor Over a year ago

Yes - copy and paste error. I don't normally use ActiveCell either. Thanks for the typo spot

Collectives™ on Stack Overflow

Find Date variable within a row and return Column VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related