VBA syntax issues

[cbrf23]

I'm having some issues with VBA in that I'm very new to it, and I'm not sure what the proper syntax is I'm supposed to be using for some of the things I want to do.

I'm trying to write a macro for excel which will accomplish the same thing as the following function:
=B3&IF(ISBLANK($C3),"",IF(ISBLANK($I$2)," ("&TEXT($C3,"0.000")&")"," ("&TEXT(($C3/25.4),"0.0000")&")"))

I tried writing this myself...I dont know if its anywhere close to right. lol. Can anyone offer any advise? Currently, I'm getting a "compile error: argument not optional" and the first line-- Sub tttTEST() -- is highlighted

I need help. Please.

Sub tttTEST()
 
Dim InDes As String, DesEmpty As Boolean, InVal As Integer, OutVal As Integer
 
'InDes could contain numbers, text, both, or a formula
'I want InDes to be whatever the cell displays
'Is "InDes As String" the appropriate choice???
 
'InVal will always be a number.
'Sometimes it will be a number generated from a formula. (like vlookup or such)
'Is "InVal/OutVal As Integer" the apporpriate choice???
 
    InDes = target.Offset(0, -2)
 
    If (Not IsEmpty(InDes)) Then
 
            DesEmpty = False
 
        Else
 
            DesEmpty = True
 
        End If
 
'Check if the cell InDes refers to is empty.  If so, DesEmpty = true, if not its False.
'Is this the right syntax for what I'm trying to do?
 
 
    InVal = target.Offset(0, -1)
'Here I am trying to take InVal, and use it to define OutVal
'OutVal can vary based on criteria related to InVal
'Am I using the proper syntax for what I want below??
 
            If InVal = "" Then 'if InVal is "", I want OutVal to also be "".
                               'I used ="", rather than is empty, in case a formula is there.
                               'Is this correct???
 
            OutVal = ""
 
            ElseIf IsEmpty.Range(I2) Then 'Check if cell I2 is empty. Needs to ALWAYS be I2. Like $I$2
 
            OutVal = Value.InVal 'If I2 is indeed empty, then OutVal should be
                                 'the number found in InVal.  I used Value.InVal
                                 'in case the cell contains a formula.
                                 'Is this correct?
 
 
            Else
 
            OutVal = Value.InVal / 25.4 ' If I2 is not empty, out OutVal needs
                                        ' to equal InVal/25.4
 
            End If
 
 
    If (Not Intersect(target, Range("D:D")) Is Nothing) Then
    'not sure exactly what Is Nothing does, I copied it off some code I used for another
    'project. Maybe someone could explain this to me???
 
            If DesEmpty = True Then
 
                    Exit Sub
 
                    ElseIf OutVal = "" Then
 
                    target = InDes.NumberFormat = "@"  'if OutVal is "" then output
'InDes and format as text
 
                    ElseIf IsEmpty.Range(I2) Then
 
 
 
                        target = InDes.NumberFormat = "@" & " (" & OutVal.NumberFormat = "#.###" & ")"
 
                    Else
 
 
 
                        target = InDes.NumberFormat = "@" & " (" & OutVal.NumberFormat = "#.####" & ")"
 
                    End If
 
            End If
 
 
End Sub

 

[Scott Huish]

First, I'm going to simplify your formula:

=B3&IF(C3="","",TEXT(C3/IF(I2="",1,25.4)," (0.0000)"))

You are referring to target in your code. Target is a system defined range in several of the Event codes. You shouldn't this as a variable as this is a keyword.

Did you want to use this as a function in the spreadsheet or what?

 

[cbrf23]

Hi Hotpepper, I appreciate you simplifying the code, but it needs to be 0.000 if standard (/1 in your code) and 0.0000 if metric (/25.4)

I'm not sure what you mean about a function in the workbook. My plan is to monitor my workbook, and call a macro whenever a certain input changes.

 

[Scott Huish]

OK:

=B3&IF(C3="","",TEXT(C3/IF(I2="",1,25.4)," (0.000"&IF(I2<>"",0,"")&")"))

What cell are you monitoring?

Is the cell changing by manual entry or by formula?