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&")"

 

[ZPhantom]

maybe under simplifying this but why not just:

 If dblNom = ""

an empty cell does not always equal a "nothing" value.

ZP

 

[rguy84]

if isEmpty(dblNom) then
  Then strOutNom = ""
Else
 strOutNom = dblNom

 

[cbrf23]

i tried = "" but still got same error.

did find a solution though.

if dblNom = 0 works...i think because its defined as a double, it has to be numerical.

i found this on another site, was helpful to me, so I thought I'd share.

| Type                                 | Test                            | Test2
| Numeric (Long, Integer, Double etc.) | If obj.Property = 0 Then        | 
| Boolen (True/False)                  | If Not obj.Property Then        | If obj.Property = False Then
| Object                               | Is obj.Property Is Nothing Then |
| String                               | If obj.Property = "" Then       | If LenB(obj.Property) = 0 Then
| Variant                              | If obj.Property = Empty Then    |

 

[cbrf23]

So, I've ran into another issue. lol.

I'm trying to determine where my output will go, based on what row my input is in.
I need to take the numerical value of that row and subtract 5 to get to the right row on my output sheet.

ie. if my input is row is 3, then I want to get to cell A8 on my output sheet...

I cant figure out how to do this...I did figure out how to get the row using .address in a message box, but when I try to use it to set my output variable I get run time error 1004 "application defined or object defined error"

heres the code I'm working on... any ideas?

Set rngInRow = ActiveCell.Rows("1:1").EntireRow '//establish starting row on input sheet
Set rngInCell = Range("I" & rngInRow.Row) '//establish starting cell on input sheet
Set rngOutRow = Rows(rngInCell.Rows("1:1").Value - 5, 1)

 

[Ralajer]

To get the row use '.Row' that returns the first row number of the range. It seems that you are already using that. Then just add or subtract from that and use Range("A" & rngInRow.Row + 5).

The problem is with this line

Set rngOutRow = Rows(rngInCell.Rows("1:1").Value - 5, 1)

The '.Value' returns what is in a cell. Be careful when subtracting to get the row as if the active cell is in row 3 subtracting 5 equals -2 which will be an error.

Also when dealing with multiple sheet it best to define the worksheet variables. The reason is that when you reference a range Range() it will default to the active sheet. This also is the a way to do what you are try to do.

Dim wsIn As Worksheet
Dim wsOut As Worksheet
Dim rngInCell  As Range
Dim inRow As Integer
Dim outRow As Integer

Set wsIn = Sheets("Input")          '// Sheet Name Input
Set wsOut  =Sheets("Output")      '// Sheet Name Output

inRow = ActiveCell.Row
outRow  = inRow + 5

Set rngInCell = wsIn.Range("I" & inRow )
Set rngOutRow = wsOut.Range("A" & outRow)

 

[cbrf23]

thanks ralajer.

wow, so there is one super obvious erorr I missed... subtracting from in row instead of adding! lol.

but, seeing what you posted, i think I better understand the .row function...

I actually have workbook and worksheet variables, I was just trying to test it by selecting a cell on the same sheet so I could see it quickly.

 

[cbrf23]

I wrote everything as a macro first so I could get it all working. I figured it would be real simple to convert it to an on change event once it was working....

Well, for the most part it was easy, but I've run into another issue.

When I wrote the code as a button called macro, I was using ActiveCell.

What I've found, is an issue when I call the function "funDescription". I have my variable "intRow" set as ActiveCell.Row, and I have a worksheet_change even that calls this function when I change a column on the input sheet.

This works great if I make a change and hit Tab. But, if I hit Enter, the active cell moves one row down, and as a result my function copies data from the wrong row...

The way data is being entered, I need it to work the same wether I press tab or enter.

I tried setting intRow as Target.Row (istead of ActiveCell.Row), but I get an error saying that variable Target is not defined...

How do I fix this? The function below is written in Sheet1 (INPUT), not in a module.

Public Function funDescription() As String
'//str for string
'//rng for range
'//lng for long
'//int for integer
'//sgl for single
'//dbl for double
'//bln for boolean
'//byt for byte
Dim strWkbk     As String '//name of workbook
Dim strInput    As String '//name of input sheet
Dim intRow      As Integer '//number active row
Dim rngRow      As Range '//active row for value aquisition
Dim rngCell     As Range '//starting cell for value aquisition
Dim strDesc     As String '//data from description field
Dim dblItem     As Double '//data from the item number field
Dim dblNom      As Double '//data from the nominal field
Dim dblPltol    As Double '//data from the tolerance + field
Dim dblMitol    As Double '//data from the tolerance - field
Dim blnMetric   As Boolean '//true or false are dimensions in metric?
Dim dblMetric   As Double '//divided by operator to get metric conversion
Dim strFormat   As String '//output number format dependendant upon metric
Dim rngInCell   As Range '//cell on row where input data is found
Dim rngOutCell  As Range '//cell on row where output data will go
Dim strOutNom   As String '//nominal output data
Dim strOutPltol As String '//+ tolerance output data
Dim strOutMitol As String '//- tolerance output data
Dim strOutTols  As String '//combined tolerance output data
Dim dblLow      As Double '//lowest value
Dim dblHigh     As Double '//highest value
Dim strOutLow   As String '//lowest value output data
Dim strOutHigh  As String '//highest value output data
Dim strOutDims  As String '//lowest/nominal/highest dimensions output data
 
strWkbk = "Book2.xls"
strInput = "INPUT"
 
If Workbooks(strWkbk).Sheets(strInput).Range("H2") = "" Then blnMetric = False Else blnMetric = True
 
intRow = Target.Row <-----help with this...
Set rngRow = Range("A" & intRow).Rows
'//establish starting cell
Set rngCell = Range("I" & rngRow.Row)
'//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
If blnMetric = True Then dblMetric = 25.4 Else dblMetric = 1
If dblMetric = 25.4 Then strFormat = "0.0000" Else strFormat = "0.000"
strOutPltol = Format(dblPltol / dblMetric, [strFormat])
strOutMitol = Format(dblMitol / dblMetric, [strFormat])
dblLow = dblNom - dblMitol
dblHigh = dblNom + dblPltol
If dblLow = 0 Then strOutLow = "" Else strOutLow = Format(dblLow / dblMetric, [strFormat])
If dblHigh = 0 Then strOutHigh = "" Else strOutHigh = Format(dblHigh / dblMetric, [strFormat])
strOutNom = Format(dblNom / dblMetric, [strFormat])
'//deterimine how tolerances will be output
If dblPltol = 0 And dblMitol = 0 Then '//if no tols, output nothing
        strOutTols = ""
    ElseIf dblPltol <> dblMitol Then '//if tols not equal, output +tol/-tol
        strOutTols = "+" & dblPltol & "/-" & dblMitol
    ElseIf dblPltol = dblMitol Then 'if tols equal, output `tols
        strOutTols = "`" & dblPltol
    End If
'//determine how low/high/nominal will be output
If dblNom = 0 Then '//if no nominal value, output nothing
        strOutDims = ""
    ElseIf blnMetric = False And dblMitol = 0 And dblPltol = 0 Then '//if no tols, and not metric, output nothing
        strOutDims = ""
    ElseIf blnMetric = True And dblMitol = 0 And dblPltol = 0 Then '//if no tols, but is metric, output nom
        strOutDims = "(" & strOutNom & ")"
    ElseIf dblMitol = 0 And dblPltol <> 0 Then '//if only + tol, output nom/high
        strOutDims = "(" & strOutNom & "/" & strOutHigh & ")"
    ElseIf dblMitol <> 0 And dblPltol = 0 Then '//if only - tol, output low/nom
        strOutDims = "(" & strOutLow & "/" & strOutNom & ")"
    ElseIf dblMitol <> 0 And dblPltol <> 0 Then '//if both tols, output low/nom/high
        strOutDims = "(" & strOutLow & "/" & strOutNom & "/" & strOutHigh & ")"
    End If
 
funDescription = strDesc & " " & strOutTols & " " & strOutDims
End Function

 

[Ralajer]

Your function can't see the target variable from the change event. The function is outside the scope of the variable. So you have to pass it with the call to the function.
In the function

'// Add intRow As Integer to the function definition
Public Function funDescription([COLOR="Red"]intRow As Integer[/COLOR]) As String
'// Remove 
Dim intRow      As Integer '//number active row
intRow = Target.Row <-----help with this...

In the Event sub

'// Add Target.Row to the function call
funDescription(Target.Row)

 

[cbrf23]

That works. Awesome! Thank you again!

So, I think I get this...
By adding (intRow As Integer)

I'm saying that there is going to be a variable "intRow" and its an integer (just like when I declare variables with dim). But this variable is going to be defined from an outside source.

Then I add (Target.Row) when I call the function, and I define that variable for that call instance.

So, can you do multiple variables that way? Like

Public Function funTest(intRow As Integer, rngRow As Range, blnRow As Boolean)

and then when I call that function

funTest(Target.Row,intRow.Rows,TRUE)

Or something like that?

 

[Ralajer]

That works. Awesome! Thank you again!

So, I think I get this...
By adding (intRow As Integer)

I'm saying that there is going to be a variable "intRow" and its an integer (just like when I declare variables with dim). But this variable is going to be defined from an outside source.

Then I add (Target.Row) when I call the function, and I define that variable for that call instance.

That correct. In programming terminology the variable in the function/sub declaration is called an argument and the argument gets passed to the function when it is called.

So, can you do multiple variables that way? Like

Public Function funTest(intRow As Integer, rngRow As Range, blnRow As Boolean)

and then when I call that function

funTest(Target.Row,intRow.Rows,TRUE)

Or something like that?

Yes you can do multiple variables. Your example would work.

There is more about functions that might be worth learning but I think you understand enough for the purposes of solving your problem.