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

 

[reberryjr]

Sorry, I should have added that the basic summary is, I want to see the minimum date in the Updated column, where the value of the Pymt Status = "Late". I'm assuming that I should make the Updated and Pymt Status columns dynamic named ranges, since rows will continuously be added.

 

[reberryjr]

I've been working on this for about 5 hours today and this is as far as I got. It's still not working.

Private Sub cobo_ClientID_Change()

Dim Sht As String
Dim LastRow As Long


Sht = Me.cobo_ClientID






With ActiveSheet
    r = Sheets(Sht).FormulaArray = "=Min(IF(R[-33]C[46]:R[-31]C[46]=""Late"",R[-33]C[-1]:R[-31]C[-1])))"
    'LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    'LastRow = Sht.FormulaArray = "=Min(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" & r).Value

 

[StephenCrump]

Can you adapt this to your layout?

Dim lStartRow As Long, lEndRow As Long
Dim ws As Worksheet

Set ws = Worksheets(Me.cobo_ClientID)  'or ActiveSheet?
lStartRow = 5

With ws
    lEndRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    .Range("E2").FormulaArray = "=MIN(IF(E" & lStartRow & ":E" & lEndRow & "=""Late"",D" & lStartRow & ":E" & lEndRow & "))"
End With

Excel 2010
	D	E
2	Result:	31 Dec 2017
3
4	Date	Status
5	5 Jan 2018	Pending
6	5 Jan 2018	Late
7	7 Jan 2018	Ontime
8	8 Jan 2018	Late
9	31 Dec 2017	Late
10	5 Jan 2018	Delivered
Sheet1

 

[reberryjr]

Not sure that solves my need. I need to be able to draw the data from the specific row, into a user form, so it can be updated when the payment comes in.

 

[StephenCrump]

Here's one way you could get the row, which is 9 for the example in Post #4

With ws
    lEndRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    s1 = "'" & ws.Name & "'!" & .Range("D" & lStartRow).Resize(lEndRow - lStartRow + 1).Address
    s2 = "'" & ws.Name & "'!" & .Range("E" & lStartRow).Resize(lEndRow - lStartRow + 1).Address
    lRowRequired = lStartRow - 1 + Evaluate("=MATCH(1,(" & s1 & "=MIN(IF(" & s2 & "=""Late""," & s1 & ")))*(" & s2 & "=""Late""),)")
End With

 

[reberryjr]

I took out one step of the formula, by ensuring that the sheet is sorted properly, every time. That means that I don't have to factor in the Updated date as a parameter. Now I just need to find the first instance of "Late" in column "AW". I tried this, but it didn't work.

Private Sub cobo_ClientID_Change()
Dim Sht As String
Dim LastRow As Long
Dim i As Long
Dim m As Variant
Dim ws4 As Worksheet
Sht = Me.cobo_ClientID
Set ws4 = ThisWorkbook.Sheets("Variables")
With ActiveSheet
    m = Application.Match(ws4.Range("D4").Value, ActiveSheet.Columns("AW"), False)
    'm = Worksheet.Function.Match("Late", ActiveSheet.Columns("AW"), 0)
    'LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    'LastRow = worksheetfunction.Min(IF(R[-33]C[1]:R[-31]C[1]=""TJ1"",IF(R[-33]C[46]:R[-31]C[46]=""Late"",R[-33]C[-1]:R[-31]C[-1])))
End With
If Not IsError(m) Then
    txt_Name = Sheets(Sht).Range("E" & CLng(m)).Value

 

[StephenCrump]

I tried this, but it didn't work.

This line of code:

m = Application.Match(ws4.Range("D4").Value, ActiveSheet.Columns("AW"), False)

will return the row number of the first occurrence of Variables!D4 (which I assume is "Late") in ActiveSheet column AW.

Given you then set txt_Name based on row m in Sheets(Sht), I am guessing that you should be looking for a Match in Sheets(Sht) rather than in ActiveSheet? (Hence my suggestion in Post #4 that you start by defining the worksheet:

Set ws = Worksheets(Me.cobo_ClientID)

If I'm on the wrong track, can you please explain what you mean by "didn't work", otherwise we can only guess.

 

[reberryjr]

So, I tried it with declaring the sheet the way you suggested and I'm getting an "object required" error.

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
[COLOR=#ff0000]Sht = Me.cobo_ClientID[/COLOR]
Set ws4 = ThisWorkbook.Sheets("Variables")
Set ws = (Me.cobo_ClientID)
With ActiveSheet
    m = Application.Match(ws4.Range("D4").Value, ActiveSheet.Columns("AW"), False)
    'm = Worksheet.Function.Match("Late", ActiveSheet.Columns("AW"), 0)
    'LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    'LastRow = worksheetfunction.Min(IF(R[-33]C[1]:R[-31]C[1]=""TJ1"",IF(R[-33]C[46]:R[-31]C[46]=""Late"",R[-33]C[-1]:R[-31]C[-1])))
End With
If Not IsError(m) Then
txt_Name = ws.Range("E" & CLng(m)).Value

 

[reberryjr]

Sorry if my questions are frustrating. This is the first VBA project I've worked on in Excel, so I'm not overly familiar with the cornucopia of functions, terms, structures, etc.