Convert Formula to VBA

[reberryjr]

I have a UserForm that looks for a sheet where the sheet name equals a combo box value. Once that sheet is found, I want certain elements from the sheet to populate the form from a specific row, based on this formula. I'm having trouble inserting the code so that it finds the correct row. I pasted the code below, along with the formula as converted via the macro recorder. The portion in red font shouldn't need to be in the formula, as the code would have already found the correct sheet.

Private Sub cobo_ClientID_Change()

Dim Sht As String
Dim LastRow As Long


Sht = Me.cobo_ClientID


With ActiveSheet
    'LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        'This is the formula that I entered into excel, converted through the macro recorder.
        Selection.FormulaArray = _
        "=MIN([COLOR=#ff0000]IF(R[-33]C[1]:R[-31]C[1]=""TJ1""[/COLOR],IF(R[-33]C[46]:R[-31]C[46]=""Late"",R[-33]C[-1]:R[-31]C[-1])))"
End With


txt_Name = Sheets(Sht).Range("E" & LastRow).Value
txt_DPStatus = Sheets(Sht).Range("F" & LastRow).Value
txt_DPPymtAmt = Format(Sheets(Sht).Range("I" & LastRow).Value, "$#,##0.00")
txt_DPFreq = Sheets(Sht).Range("K" & LastRow).Value
txt_DCStatus = Sheets(Sht).Range("M" & LastRow).Value
txt_DCPymtAmt = Format(Sheets(Sht).Range("P" & LastRow).Value, "$#,##0.00")
txt_DCFreq = Sheets(Sht).Range("R" & LastRow).Value
txt_OCStatus = Sheets(Sht).Range("T" & LastRow).Value
txt_OCPymtAmt = Format(Sheets(Sht).Range("W" & LastRow).Value, "$#,##0.00")
txt_OCFreq = Sheets(Sht).Range("Y" & LastRow).Value
txt_CTIStatus = Sheets(Sht).Range("AA" & LastRow).Value
txt_CTIPymtAmt = Format(Sheets(Sht).Range("AD" & LastRow).Value, "$#,##0.00")
txt_CTIFreq = Sheets(Sht).Range("AF" & LastRow).Value
txt_CTOStatus = Sheets(Sht).Range("AH" & LastRow).Value
txt_CTOPymtAmt = Format(Sheets(Sht).Range("AK" & LastRow).Value, "$#,##0.00")
txt_CTOFreq = Sheets(Sht).Range("AM" & LastRow).Value
txt_TotalDue = Format(Sheets(Sht).Range("AO" & LastRow).Value, "$#,##0.00")
txt_Week1 = Format(Sheets(Sht).Range("AP" & LastRow).Value, "$#,##0.00")
txt_Week2 = Format(Sheets(Sht).Range("AQ" & LastRow).Value, "$#,##0.00")
txt_Week3 = Format(Sheets(Sht).Range("AR" & LastRow).Value, "$#,##0.00")
txt_Week4 = Format(Sheets(Sht).Range("AS" & LastRow).Value, "$#,##0.00")
txt_Week5 = Format(Sheets(Sht).Range("AT" & LastRow).Value, "$#,##0.00")
txt_TotalPaid = Format(Sheets(Sht).Range("AU" & LastRow).Value, "$#,##0.00")
txt_NetDue = Format(Sheets(Sht).Range("AV" & LastRow).Value, "$#,##0.00")


End Sub

 

[StephenCrump]

I have made just a couple of changes to your code:

Private Sub cobo_ClientID_Change()

    Dim ws As Worksheet, ws4 As Worksheet
    Dim m As Variant
    
    Set ws = Worksheets(Me.cobo_ClientID.Value)
    Set ws4 = ThisWorkbook.Sheets("Variables")
    
    With ws
        m = Application.Match(ws4.Range("D4").Value, .Columns("AW"), False)
        If Not IsError(m) Then
            txt_Name = .Range("E" & m).Value
            txt_DPStatus = .Range("F" & m).Value
            txt_DPPymtAmt = Format(.Range("I" & m).Value, "$#,##0.00")
            txt_DPFreq = .Range("K" & m).Value
            txt_DCStatus = .Range("M" & m).Value
            txt_DCPymtAmt = Format(.Range("P" & m).Value, "$#,##0.00")
            txt_DCFreq = .Range("R" & m).Value
            txt_OCStatus = .Range("T" & m).Value
            txt_OCPymtAmt = Format(.Range("W" & m).Value, "$#,##0.00")
            txt_OCFreq = .Range("Y" & m).Value
            txt_CTIStatus = .Range("AA" & m).Value
            txt_CTIPymtAmt = Format(.Range("AD" & m).Value, "$#,##0.00")
            txt_CTIFreq = .Range("AF" & m).Value
            txt_CTOStatus = .Range("AH" & m).Value
            txt_CTOPymtAmt = Format(.Range("AK" & m).Value, "$#,##0.00")
            txt_CTOFreq = .Range("AM" & m).Value
            txt_TotalDue = Format(.Range("AO" & m).Value, "$#,##0.00")
            txt_Week1 = Format(.Range("AP" & m).Value, "$#,##0.00")
            txt_Week2 = Format(.Range("AQ" & m).Value, "$#,##0.00")
            txt_Week3 = Format(.Range("AR" & m).Value, "$#,##0.00")
            txt_Week4 = Format(.Range("AS" & m).Value, "$#,##0.00")
            txt_Week5 = Format(.Range("AT" & m).Value, "$#,##0.00")
            txt_TotalPaid = Format(.Range("AU" & m).Value, "$#,##0.00")
            txt_NetDue = Format(.Range("AV" & m).Value, "$#,##0.00")
        End If
    End With

End Sub

If your code shows the UserForm, and you change the client ID combobox to TJ1, the UserForm will populate with values from Row 2 of sheet "TJ1". I'm not sure if that's exactly what you want, but at least the code does run OK.

 

[reberryjr]

@StephenCrump, one last question on this topic, and I think I can put this one to bed. In reviewing the need, I actually need "m" to equal the first instance in "AW" that is either "Late" OR "Current". I looked at and tried a couple of different ways, without finding the desired result. Here are the ways that I could achieve what I'm looking for, in "my" terms:

m = AW <> Paid (Paid is in ws4.range("D2")
m = AO - AU > 0.00

Thoughts on best approach?

 

[reberryjr]

I've been perusing the web and have tried these lines, to no avail

    'm = Application.Match(ws4.Range("D4").Value, .Columns("AW"), False) Or Application.Match(ws4.Range("D2").Value, .Columns("AW"), False)    
    'm = ws.Range("AV").Value <> "0"
    'm = ws.Range("AV").Value <> 0
    'm = Application.Match(ws.Range("AV").Value) <> 0
    'm = Application.Match(ws.Range("AV").Value <> 0)
    'm = Offset(Application.Match(ws4.Range("D4").Value, .Columns("AW"), False), Application.Match(ws4.Range("D2").Value, .Columns("AW"), False))