MACRO: IF THEN Statement (Multiple Criteria Match then Copy)

[Paul_Hossler]

Line numbers are easy to handle -- the function FindLastRowWithData

Function LastRowWithData(ws As Worksheet, Optional MaxNumCellsUsed As Long = 1) As Long
    Dim i As Long
    
    For i = ws.UsedRange.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(ws.Rows(i)) > MaxNumCellsUsed Then
            LastRowWithData = i
            Exit Function
        End If
    Next i
End Function

'backs up' the used range until it finds a row with more than MaxNumCellsUsed (default = 1) of cells with something in them and assumes that that is the row where data ends

The For loops than can stop checking at that row since that's the end of 'meaningful' data

[pawcoyote]

Ok I get that.. Roger that...

[Paul_Hossler]

OK,

1. When I made the Deployment2MAFFT macro from the MAAFT2Deployment, I didn't reverse everything that should have been

2. It looks like the format changed a little, and that messed up the Const values that hold the headers in the module Globals

Try this version -- it should be closer

[pawcoyote]

Ok, I added in data on both sheets 41 on MAFFT and 68 on the deployment. The Copy from the MAFFt to the Deployment works great! But I get an error when I run the Deployment2MAFFT Macro.

IF iMAFFT = 4 And iDeploy = 10 THEN Stop

I am not sure what that is looking at and why the error...

[Paul_Hossler]

Oops - my bad - I forgot to delete that

That was for debugging - just delete the line

[pawcoyote]

Roger that, one question what was that looking at so I can go to the sheets and see what data it was looking at. Edification. :-)

[pawcoyote]

I have been testing. I am getting the following errors when I go to run the Macro to copy the information from the Deployment to the MAFFT.

owned by customer_error.jpgrtn lease error.jpg

It seems like it is looking at column BJ Contract Type on the Deployment sheet for some data.

[Paul_Hossler]

Roger that, one question what was that looking at so I can go to the sheets and see what data it was looking at. Edification. :-)

Row 4 on MAFFT and row 10 on Deployment were a match on the 2 two fields, but were not copying the fields over

I stopped the macro there and then single stepped through to see why the fields were not being copied over

That's how I found the line where I missed reversing the MAFFT and Deployment references

[Paul_Hossler]

I have been testing. I am getting the following errors when I go to run the Macro to copy the information from the Deployment to the MAFFT.

It seems like it is looking at column BJ Contract Type on the Deployment sheet for some data.

Polished the error message wording a little bit and corrected the text index

Capture.JPG