Convert Formula to VBA

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
499
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
499
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Mar 16, 2017
Messages
499
Office Version
  1. 2013
Platform
  1. Windows
I've been working on this for about 5 hours today and this is as far as I got. It's still not working.

Code:
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
Can you adapt this to your layout?

Code:
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

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Result:</td><td style="text-align: right;background-color: #FFFF00;;">31 Dec 2017</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Status</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5 Jan 2018</td><td style=";">Pending</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5 Jan 2018</td><td style=";">Late</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">7 Jan 2018</td><td style=";">Ontime</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">8 Jan 2018</td><td style=";">Late</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">31 Dec 2017</td><td style=";">Late</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">5 Jan 2018</td><td style=";">Delivered</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 
Last edited:

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
499
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
Here's one way you could get the row, which is 9 for the example in Post #4

Code:
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
 
Last edited:

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
499
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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.

Code:
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
I tried this, but it didn't work.

This line of code:
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:

Code:
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

Active Member
Joined
Mar 16, 2017
Messages
499
Office Version
  1. 2013
Platform
  1. Windows
So, I tried it with declaring the sheet the way you suggested and I'm getting an "object required" error.

Code:
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

Active Member
Joined
Mar 16, 2017
Messages
499
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,311
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top