help with conveting excel formula to vba??

[cbrf23]

Hello,

I'm new to VBA and writing macros, but now that I've discovered it, I really want to use it for a current project because I know that it will make things a LOT easier and a LOT less complicated if I could do what I'm currently by using a macro in VBA.

I would really appreciate some help with this!

Lets say I'm using the following formula, which I have pasted all down column D. Based on whatever row we've pasted this into, were going to see whats in column B, then check whats in column C, then check to see if I2 contains anything (this is a box I use to switch between metric and standard), and based on all this criteria, we output something like:
whateverswritteninB3 (0.000)

=B3&IF(ISBLANK($C3),"",IF(ISBLANK$I$2)," ("&TEXT($C3,"0.000")&")"," ("TEXT(($C3/25.4),"0.0000")&")"))

I'd really like to do this in VBA.
I dont know the proper syntax, but in my head I'm thinking

dim InDes as ??, InVal as ??
InDes = target.offset(0,-2) 'this cell could contain text or a formula...in I want to display whatever the cell is actually showing. 
InVal = if target.offset(0,-1) = "" then InVal = "" 
else
if cell I2 is empty, then InVal = value.target.offset(0,-1)
else
if cell I2 is not empty, then InVal = value.target.offset(0,-1)/25.4
 
If (Not Intersect(Target, Range("D:D")) Is Nothing) Then
            If (Not IsEmpty(Target)) Then
                    'help with syntax here
                    if InVal = "" then
                    target = InDes
                    else
                    target = essentially the equivalent of:
                    InDes&" ("&InVal&")"

 

[Ralajer]

I'm not sure when or how you wanted the macro to be run or where the data starts i.e. headers. The below must be manually called and will apply the the conversion to D2 to D(Used Range row). Let me know if you wanted it to be automatic on a particular event i.e. changing the value in I2, Column B and/or C.

Sub foo()
    
    Dim cell As Range
    Dim CF As Double
    Dim s As String
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    If Range("I2").Value = "" Then CF = 1 Else CF = 25.4

    For Each cell In Intersect(UsedRange, Range("D2:D" & Rows.Count))
        s = cell.Offset(0, -2)
        If cell.Offset(0, -1).Value <> "" Then
            s = s & " (" & Format(cell.Offset(0, -1).Value / CF, "0.000") & ")"
        End If
        cell.Value = s
    Next cell
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[cbrf23]

Hi Ralajer,

First, thank you very much for the help!

Second, to answer your questions:

The formula I posted above is a simplified example to represent what I'm currently using in my worksheet. I figured that if I can get the basic principles down with this formula, then I can expand upon it. I'm pretty unfamiliar with VBA and without knowing the proper syntax its like trying to explain directions to someone who doesnt speak english.

What I'd like to use it for, is to check for all the input on one sheet, and then automatically create an entry on another sheet. On the input sheet, each column contains specific data, and I need to evaluate that data, combine it and output it in the proper format on the second sheet.

The current way I'm doing this has columns A-E used for data entry. Column F has a large nested if's formula pasted in each row all the way down. This formula evaluates the data in columns C,D,and E, checks cell I2 to determine metric/standard conversion, then outputs everything in the correct format based on the data entered. Column A is the item number, and Column B is the description.

On the second sheet I have a formula that apends the output from Column F on the first sheet with the description entered in column B on the first sheet.

I originally wanted this to be one process, but because the format needs to change based on what is input, the formula became a lot longer than 1024 characters, so I split it into 2 steps.

What I'd like to do is eliminate the need for these formulas, and write a macro that will monitor sheet one for new data, and automatically create my list on sheet 2.

This will simplify a lot of other things for me and will eliminate a lot of headaches.

 

[cbrf23]

In case you are wondering, the two part formulas I'm currently using are:

[B][FONT=Times New Roman]OUTPUT FORMULA (on INPUT sheet):<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/B]
[FONT=Times New Roman][COLOR=black]=IF(AND(ISBLANK($D3),ISBLANK($E3)),IF(ISBLANK($I$2),"",IF(ISBLANK($C3),""," ("&TEXT(($C3/25.4),"0.0000")&")")),IF($D3=$E3," `"&$D3&" ("&IF(ISBLANK($I$2),TEXT(($C3-$E3),"0.000")&"/"&TEXT(($C3),"0.000")&"/"&TEXT(($C3+$D3),"0.000")&")",TEXT((($C3-$E3)/25.4),"0.0000")&"/"&TEXT(($C3/25.4),"0.0000")&"/"&TEXT((($C3+$D3)/25.4),"0.0000")&")"),IF($D3=0," +0.000/-"&TEXT(($E3),"0.000")&" ("&IF(ISBLANK($I$2),TEXT(($C3-$E3),"0.000")&"/"&TEXT($C3,"0.000")&")",TEXT((($C3-$E3)/25.4),"0.0000")&"/"&TEXT(($C3/25.4),"0.0000")&")"),IF($E3=0," +"&TEXT(($D3),"0.000")&"/-0.000 ("&IF(ISBLANK($I$2),TEXT($C3,"0.000")&"/"&TEXT(($C3+$D3),"0.000")&")",TEXT(($C3/25.4),"0.0000")&"/"&TEXT((($C3+$D3)/25.4),"0.0000")&")")," +"&TEXT(($D3),"0.000")&"/-"& TEXT(($E3),"0.000")&" ("&IF(ISBLANK($I$2),TEXT(($C3-$E3),"0.000")&"/"&TEXT(($C3),"0.000")&"/"&TEXT(($C3+$D3),"0.000")&")",TEXT((($C3-$E3)/25.4),"0.0000")&"/"&TEXT(($C3/25.4),"0.0000")&"/"&TEXT((($C3+$D3)/25.4),"0.0000")&")")))))<o:p></o:p>[/COLOR][/FONT]
<o:p></o:p>
[B][FONT=Times New Roman]APPEND DIMENSIONS FORMULA (on sheet 2):<o:p></o:p>[/FONT][/B]
<o:p></o:p>
[FONT=Times New Roman]=IF(ISBLANK(INPUT!$B3),"", INPUT!$B3&INPUT!$F3)<o:p></o:p>[/FONT]

 

[cbrf23]

I'm getting an error, "Runtime error 424: Object required" and the line being highlighted is

For Each cell In Intersect(UsedRange, Range("D2:D" & Rows.Count))

 

[Ralajer]

You can probably forget about the error as that code won't be needed based on how you want the code to be executed. The reason for the error though is that column D must not overlap the used range in your sheet, so the intersect function is return an empty range.

The below is a template of one way to get the code to be called if a change occurs in cells A2:EXXX.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column <= Columns("E").Column And Target.Row > 2 Then
        Dim output As String
        Dim CF As Double
        
        Dim a, b, c, d, e

        If Range("I2").Value = "" Then CF = 1 Else CF = 25.4
        
        a = Range("A" & Target.Row).Value
        b = Range("B" & Target.Row).Value
        c = Range("C" & Target.Row).Value
        d = Range("D" & Target.Row).Value
        e = Range("E" & Target.Row).Value
        '// Handles all the conversion and concatenation in separate function.
        '// makeString Function is below
        output = makeString(a, b, c, d, e, CF)
        '// Output result to Output sheet.
        Sheets("Output").Range("A" & Target.Row).Value = output
    End If
End Sub

I tried to figure out what the function was meant to do but it's hard to follow and it would probably take longer that I would be willing to spend. I can help with syntax and code if what the function was meant to do was a explained less cryptically.

The below is the start a function that would do the conversion and joining etc.
Private Function makeString(a, b, c, d, e, CF As Double) As String
Dim output As String
'// Conversion of your function to the macro
makeString = output '\\"STRING YOU MADE"
End Function

 

[cbrf23]

Hi Ralajer,
I'm a little confused by the whole "function" thing. I pasted the code you provided in a new workbook, and pasted the private function above that. I did not get any errors, but I also did not get any output...

I dont quite understand the whole "function" thing...is that similar to just calling a macro?

I have an idea on how to create the string. I will try to put something together today and post it up, and maybe that will help get my intentions across better.

I know the excel formula i posted does nothing to help, but the way I need to output the data relies on a lot of variables and I feel like it would be really difficult to explain not in person.

Thanks again for all the help, I greatly appreciate your time and effort!

 

[Ralajer]

A function in vba is like a sub except that it can return a value. It can also be used in excel like a built-in function such as TEXT.

Example Function

Function AreaOfCircle(R As Double) As Double
    Const pi As Double = 3.14159265358979
    AreaOfCircle = pi * R ^ 2
End Function

It can be called by a macro like this

Sub example()
    Dim Radius As Double
    Dim Area As Double
    Radius = 1
    Area = AreaOfCircle(Radius)
    MsgBox Area
End Sub

Or it can be called from a cell in excel

[A1] = 1
[B1] = AreaOfCircle(A1) | Displays 3.14159265358979

So in the case of your problem the function gets passed the values in the various cells a-e and the conversion factor. Does whatever you need it to do, converting and joining the values into the desired output. Then returns that to the main SUB and gets assigned to the desired cell. The reason I would recommend using a function over just including the code in the SUB is that it may need to be called for different situations and repeating the code for each situation is harder to manage if you need to change it.

My apologies for not stating it explicitly but the previous code wasn't meant to do anything. It was only meant to show the syntax that was required to get the code to run when you changed cells in a particular range. The below function is example of how the function will work if your problem was a little simpler.

Function makeString(a, b, c, d, e, CF As Double) As String
    Dim output As String
    Dim fmt As String
    fmt = "0.0000"
    
    output = a & " - " & b & " - " & c & " - " & Format(d / CF, fmt) & " - " & Format((e - d) / CF, fmt)
    
    makeString = output
End Function

Feel free to ask question I'm sure that I haven't explained everything as well as I could've.

 

[cbrf23]

Wow, that is too cool.

I can see how that would come in handy. Especially with what I want to do. Basically, just write my excel formula as a function, and then I can call it whenever I need it.

That is fantastic!

I will be working on this as I get time, so I'm sure I will have more questions as I go Thanks so much, this is a great community!

 

[cbrf23]

So, everything is going pretty well...but I seem to have something wrong here since I'm getting a compile error "type mismatch"

Basically I want to say

If dblNom "is empty" '<--------need to know how to say this in vba speak...
Then strOutNom = ""
Else
strOutNom = dblNom

I've tried dblNom Is Nothing and dblNom = "" but I get the same error either way...
Is this because I've dimmed dblNom as a double? or am I doing something else wrong here?


Here is the chunk of code where I'm getting the error:

For Each rngLine In rngTarget
'//establish starting row
Set rngRow = ActiveCell.Rows("1:1").EntireRow
'//establish starting cell
Set rngCell = Range("I" & rngRow.Row)
'//check that active row (rngRow) intersects with rngTarget
If Application.Intersect(rngTarget, rngRow) Is Nothing Then
'//if it is not within rngTarget:
MsgBox ("End of the Line")
Exit Sub
Else '//if it is within rngTarget:
'//establish values
dblItem = rngCell.Offset(0, -8).Value
strDesc = rngCell.Offset(0, -7).Value
dblNom = rngCell.Offset(0, -6).Value
dblPltol = rngCell.Offset(0, -5).Value
dblMitol = rngCell.Offset(0, -4).Value
strMethod = rngCell.Offset(0, -3).Value
strZone = rngCell.Offset(0, -2).Value
If blnMetric = True Then dblMetric = 25.4 Else dblMetric = 1
If dblMetric = 25.4 Then dblFormat = "0.0000" Else dblFormat = "0.000"
If dblNom Is Nothing Then strOutNom = "" Else strOutNom = dblNom  <-----error here, this line highlighted

Any suggestions?