CODEWITHSUNDEEP
excelvbareferencecell
Neb967
Neb967

Reputation: 33

How to reference same dates between worksheets?

How can I take the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a loop?

    Dim wb As Workbook
    Dim ws1 As Worksheet

    Set ws1 = wb.Sheets(1)

    Dim Lastrow_ws1 As Long
    Lastrow_ws1 = ws1.Range("A" & Rows.Count).End(xlUp).Row

    Dim LastDate As Long

 ** Set LastDate = something 'take the last date in ws3 column A and find that date in ws1 column A and find the return the cell reference**

        For y = LastDate To Lastrow
            ws1.Cells(y, "A").Copy
            ws2.Range("A1").PasteSpecial Paste:=xlPasteValues

            Calculate

'THEN RUN A SEPERATE LOOP USING THE LASTDATE VARIABLE

Upvotes: 0

Views: 509

Answers (1)

itChi
itChi

Reputation: 672

I can help you out, but you'll need to be a bit clearer on the question, and the formatting of your question, you also usually need to be specific as to what your issue is. You can use something like this to get started formatting your code.

Right, so to start of with, you define wb with Dim wb As Workbook but you don't set it to anything. I might suggest Set wb = ActiveWorkbook before you call it with Set ws1 = wb.Sheets(1)

Hard to tell from your code, but Dim Lastrow_ws1 As Long Lastrow_ws1 = ws1.Range("A" & Rows.Count).End(xlUp).Row should ideally be across different lines:

Dim Lastrow_ws1 As Long
Lastrow_ws1 = ws1.Range("A" & Rows.Count).End(xlUp).Row

Now, where you venture into pseudo code: Set LastDate = something 'take the last date in ws3 column A and find that date in ws1 column A and find the return the cell reference

You must define ws3 it's not mentioned anywhere.

In this line ws1.Cells(y, "A").Copy ws2.Range("A1").PasteSpecial Paste:=xlPasteValues You mention ws2, but it's not defined anywhere.

The loop could be something like:

For i in Range("A1","A" & Lastrow_ws1)
If i = LastDate Then
    'Do what you want to do
Else
End If
Next

Upvotes: 3

Related Questions