VBA Numeric Value Find / Overwrite data

Question

I am in the process of writing a macro that allows me to update data monthly. However, I realized that sometimes I will need to overwrite the data from the same month when there is a correction issued to the data. I am trying to come up with a macro that will allow me to search the entire column and if there is a match with the data, allow me to run another macro to overwrite the old data with the new data. Any ideas of how to go about this?

Here is what I have so far. I need to replace to i to 500 with the entire column.

Sub FindMatchingValue()
Dim i As Integer, ValueToFind As Integer
intValueToFind = Sheet8.Range("L6")
For i = 1 To 500    ' This needs to be the entire column
    If Cells(i, 1).Value = intValueToFind Then
        MsgBox ("Found value on row " & i)
        Exit Sub
    End If
Next i

MsgBox ("Value not found in the range!")
End Sub

Record a macro then review what code you have when you've done what you described above. — Tim Wilkinson, Jun 13 '18 at 20:50
I have the framework for the code. The only issue I run into is being able to search the entire column and match those dates with the date on another sheet. — TDJ, Jun 13 '18 at 20:56
If you have the code please post it in your question highlighting which bit isn't working. — Tim Wilkinson, Jun 13 '18 at 20:57
If you need to replace i to 500 with a column I don't understand why you're looping through the whole column to then overwrite it? — Tim Wilkinson, Jun 13 '18 at 21:41
What I mean that instead of searching i to 500, I need to search the entire column. Originally I only needed to search the first 500 cells, but the document size is going to grow over time now and I need it to search through the whole column in order to account for the growth of the document in the future. — TDJ, Jun 13 '18 at 21:44
https://stackoverflow.com/questions/14931700/how-to-find-a-value-in-an-excel-column-by-vba-code-cells-find — Tim Wilkinson, Jun 13 '18 at 21:47

urdearboy · Accepted Answer · 2018-08-11T18:29:20.927

You do not want to run a loop down your entire column (slightly over 1 mil X). Instead, find your last row form the bottom, and loop through that range.

If your goal is to run a second Macro when you do find a match, you can get rid of your msgbox and Exit Sub and replace with Call SecondMacro, where "SecondMacro" is the name you assigned to your sub of course. Just an option ~

Sub FindMatchingValue()

Dim i As Integer, ValueToFind As Integer, LRow as Integer
intValueToFind = Sheet8.Range("L6")

LRow = Range("A" & Rows.Count).End(XlUp).Row

For i = 1 To LRow
    If Cells(i, 1).Value = intValueToFind Then
        MsgBox ("Found value on row " & i)
        Exit Sub
    End If
Next i

MsgBox ("Value not found in the range!")
End Sub

VBA Numeric Value Find / Overwrite data

1 Answers1