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]

You need:

Sht = Me.cobo_ClientID
Set ws = Worksheets(Sht)


'or in one step
Set ws = Worksheets(Me.cobo_ClientID)

Then you can replace all subsequent Sheets(Sht). qualifiers with ws.

 

[reberryjr]

I'm not sure what I'm doing wrong here. I'm getting a type mismatch at the red font line.

Private Sub cobo_ClientID_Change()

'Dim Sht As String
'Dim LastRow As Long
'Dim i As Long
Dim m As Variant
Dim ws As Worksheet
Dim ws4 As Worksheet
'Sht = Me.cobo_ClientID
Set ws4 = ThisWorkbook.Sheets("Variables")
[COLOR=#ff0000]Set ws = Worksheets(Me.cobo_ClientID)[/COLOR]


With ActiveSheet


    m = Application.Match(ws4.Range("D4").Value, ws.Columns("AW"), False)
    'LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row


End With


If Not IsError(m) Then
txt_Name = ws.Range("E" & (m)).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 If


End Sub

 

[StephenCrump]

Sorry, my mistake:

Set ws = Worksheets(Me.cobo_ClientID[COLOR=#ff0000][B].Value[/B][/COLOR])

(Ideally, we should trap an error if the ComboBox's value is not a valid worksheet name).

 

[reberryjr]

I tried it both ways and still get a Type Mismatch error.

Set ws = Worksheets(Me.cobo_ClientID).Value
'Set ws = Worksheets(Me.cobo_ClientID.Value)

 

[StephenCrump]

Are you happy to post your workbook? ... it will probably speed things up a bit if I could take a look.

I may be off-line for a few hours now though.

 

[reberryjr]

hmmm...my understanding is that I can't attach a copy of the file...only screenshots. Am I mistaken?

 

[StephenCrump]

hmmm...my understanding is that I can't attach a copy of the file...only screenshots. Am I mistaken?

Correct. You can't attach a file directly.

So you'll need to upload to a file sharing site (e.g. I use box.com) and post a link.

(Provided you're happy to share the workbook, e.g. no sensitive data).

 

[reberryjr]

Nah, there's nothing sensitive in there. Here's the link, before I throw my laptop through the wall. LOL!

https://app.box.com/s/6b9g7hkwfngt5qefub6ibthc88kndomb

 

[StephenCrump]

Here's the link ...

Thanks. How are you running Private Sub cobo_ClientID_Change()?

This is event code, which will be triggered by a change in the Combobox's value. But at the moment, your code doesn't appear to be showing the UserForm at all?

If I run the code: frm_UpdatePayments.Show

and then select "TJ1" in the ComboBox of the UserForm, then

Set ws = Worksheets(Me.cobo_ClientID.Value)

will correctly set ws to Worksheets("TJ1"), i.e. working as intended?

 

[reberryjr]

I haven't attached the form to a show event yet. But he's, when you select TJ1 from the combo box, it hits the sheet and pulls the name in. I got super frustrated, so was starting over with the client I'd change.