Compare Rows to Columns in Different Sheets

[weaverae]

Hello Everyone,

I am very to new to VBA and have run into some difficulties early on. Currently I have a tabs "IDB_CapEx", "IDB_OpEx", and "RCM".

The RCM contains a variable number of columns with the row information as follows:
Row 2: Project Name
Row 3: Project Number
Row 4: Project Manager
Row 5: Release Date

If the Project number contains "Opex"; the Project Name, Project Manager, and Release Date are compared to the "IDB_OpEx" sheet. If Project Name isn't found, it creates a new entry at the end of the list. If it is found, it updates that project to match the "RCM" information.

I know this is very specific, but I wanted to give as much background as possible. My question is, how do I go about having the script go through an unspecified number of rows and then compare that to columns in another sheet?

Any help would be much appreciated. This is just the beginning of what I want to write but also want to learn for the future.

Thanks.

 

[DeBeuz]

Hi,

When the number of columns or rows in not known yet, I usually search for an 'always filled' column or row.
Lets say for example that column A is always filled and you wanted to match a value to the values in column C that is not always filled.
I would loop over all rows untill columns A is empty like this

    Dim FoundIt As Boolean
    Dim MatchRow As Long
    Dim R As Long
    
    'First row to try for matching
    R = 1
    FoundIt = False
    MatchRow = 0
    'Loop untill column A is empty
    While Not FoundIt And Sheets("Sheet1").Cells(R, "A").Value <> ""
        If Sheets("Sheet1").Cells(R, "C").Value = WhatImLookingFor Then
            MatchRow = R
            FoundIt = True
        End If
        'Try next row
        R = R + 1
    Wend
    'Here you can process row MatchRow

Does this help?