Determining the cell address to know where my loop ends - EXCEL VBA

Question

Hi I am wondering how to use .address to determine on what row should I stop in my iteration. So my code is finding the cell address of a predetermined row of dates (weekly) and when I input a date, it will automatically look for the match/closest one. My problem is to put a value to the next column of the row of dates depending on the column on which the closest/match dates is in.

z = CDate(x)
MsgBox z

For Each find In Sheet2.Range("B4:B183")

    If find = z Then
    MsgBox "found at :" & find.Address
    
        Do Until Sheet2.Range("C" & PaidRow) > find.Address
            Sheet2.Range("C" & PaidRow).Value = 0
        Loop
    End If 
Next

This is the part of the code where I should determine the address of the closest/match date.

First is I converted the x value which is my closest/match date to actual date and not excel values e.g. 44506 = 11/6/2021 and then the for loop is for me to determine the find.address which is correct when I put a msgbox find.address.

But I can't seem to use find.address outside the original for loop.

Scott Holtzman · Answer 1 · 2020-09-06T14:04:25.473

0

You can find and use the cell where the date is found like this:

z = CDate(x)

Dim found as Range
Set found = Sheet2.Range("B4:B183").Find(z,lookat:=xlWhole)

If not found Is Nothing Then

    MsgBox "found at :" & found.Address

    found.Font.Bold = True 'for example

End If

edited Sep 06 '20 at 14:04

answered Sep 05 '20 at 23:50

Scott Holtzman

27,099
5
37
72

Scott, if you are using the variable `found` wouldn't the `MsgBox` be `& found.Address` – GMalc Sep 06 '20 at 00:51
Thanks Scott! Quick question though, Can I use sheet2.range("C" & PaidRow).value for my loop where paidrow = sheet2.range("c999").end(xlUp).Row + 1 <-- meaning it's a dynamic array? – Charlie C Sep 06 '20 at 15:58
@CharlieC Since `PaidRow + 1` is an empty cell because `PaidRow` is the last row with data, then you are asking if the blank cell's value can be used. Try `Debug.Print sheet2.range("C" & PaidRow).value`, what value do you see in the `Immediate window` ? When using the last row variable +1 you are usually wanting to write data to the blank cell or row. – GMalc Sep 06 '20 at 17:58
I'm sorry I'm afraid I didn't explain my question well. What I was trying to say is on my Column C where it is dynamic, I want to do a loop there where values will be zeroes until I'm with the same row with my find.address which is on my Column B. – Charlie C Sep 06 '20 at 20:49

score 0 · Answer 2 · answered Sep 06 '20 at 00:27

Find can't find an approximate match, only exact. Try using Worksheetfunction.MATCH which requires data to be sorted if an approx match is desired.

Depending upon sorting sequence you either find an exact match or the next bigger/smaller approximation. Your loop can then use the sorted values to determine when to stop.

For example, you want all values from July and your dates are sorted in ascending order. So you look for a match of July 1. You find either that or the last date before that (probably June). Therefore the next date must be in July if there are any entries for July at all.

So, you look at each entry until you encounter a date that isn't in July. That's when you stop processing.

score 0 · Answer 3 · edited Sep 07 '20 at 01:50

So I have figured out how to do it I don't know if it's efficient but it' working though, thanks for all your inputs Gmalc, Scott, and Variatus.

Here is my updated code based on Scott's answer.

z = CDate(x)

Set find = Sheet2.Range("B4:B183").find(z, lookat:=xlWhole)
 
Dim InfoCol, InfoRow, LastItemRow, MatchDate As Long

LastItemRow = find.Row

For MatchDate = PaidRow To LastItemRow

If LastItemRow = MatchDate Then

    Sheet2.Cells(MatchDate, 3).Value = Range("G6").Value
    Sheet2.Cells(MatchDate, 4).Value = Range("E8").Value
    Sheet2.Cells(MatchDate, 1).Value = Range("E6").Value
    
    Else: 'Sheet2.Cells(MatchDate, 3).Value = .Range(Sheet2.Cells(MatchDate, 3).Value).Value
           
           Sheet2.Cells(MatchDate, 3).Value = 0
           Sheet2.Cells(MatchDate, 4).Value = Range("E8").Value
           Sheet2.Cells(MatchDate, 1).Value = Range("E6").Value
    
        
        
End If

Next MatchDate

Determining the cell address to know where my loop ends - EXCEL VBA

3 Answers3