Storing variables in a macro and using those variables to performcalculations.

[greg.moberg]

Hello Experts,

I am brand new to VBA, and am having a few problems writing a macro.
I would like to have the macro search through my data and find the
PT1, PT2, and 2. Whenever one of these is found, I'd like some data
from the same row to be stored as a variable. Once a variable for 2
is stored, I'd like it to perform a series of calculations and display
the results. I'm using select case because the data is not in any
predictable order. My code is below:

Static Sub IterativeCalc()

' Keyboard Shortcut: Ctrl+Shift+I

Dim EbiPT1, EbiPT2, EbiQC2, MC, MpH As Double

I2 = Range("$I$2").Value
E3 = Range("$E$3").Value
I3 = Range("$I$3").Value
F3 = Range("$F$3").Value
E2 = Range("$E$2").Value


Dim cell As Range
For Each cell In Range(ActiveCell, ActiveCell.End(xlDown))
With cell

' First, for PT1, PT2, and 2. The Calculations will be performed
once the macro finds 2.

Select Case .Text

Case "PT1"
ActiveCell.Offset(0, 2).Value = EbiPT1
ActiveCell.Offset(1, 0).Activate

Case "PT2"
ActiveCell.Offset(0, 2).Value = EbiPT2
ActiveCell.Offset(1, 0).Activate

Case "2"
ActiveCell.Offset(0, 2).Value = EbiQC2
ActiveCell.Offset(1, 0).Activate


'Now we will perform the calculations

ActiveCell.Offset(0, 7).Value = (EbiQC2 - EbiPT2) /
(Log((I3 / 1000) / (F3 / 1000)) / Log(10#))
ActiveCell.Offest(0, 7).Value = MC
ActiveCell.Offset(1, 7).Value = (EbiQC2 - EbiPT1) / (I2 -
E2)
ActiveCell.Offset(1, 7).Value = MpH
ActiveCell.Offset(2, 7).Value = EbiPT2 - MC * (Log(F3 /
1000) / Log(10#))
ActiveCell.Offset(3, 7).Value = EbiPT1 - MpH * E2


End Select

End With
Next cell

End Sub

It seems like the macro is scanning through my data, but I can't get
it to do the calculations. I've also written custom functions to
perform the calculations, but when I use these, it still doesn't
work.

Any help is greatly appreciated.

Thanks!

Greg

 

[Joel]

Your problem is activecell is the wrong way of writing to the cells. Make
change like this

from
Case "PT1"
ActiveCell.Offset(0, 2).Value = EbiPT1
ActiveCell.Offset(1, 0).Activate
to
Case "PT1"
.Offset(0, 2).Value = EbiPT1

cell is the location on the wroksheet that you are reading. I think you
want to want to write two column to the left of cell. There is no need to
activate the cells. You arre already moving through the worksheet (moving
down a column) using the line below. You don't havve to activate a cell to
read or write.

For Each cell In Range(ActiveCell, ActiveCell.End(xlDown))

 

[Nigel]

You are storing the values of the variables to the sheet, I think you want
this the other way around?
e.g.

The variable EbiPT1 has been defined but no value has been assigned! So
when you use

ActiveCell.Offset(0, 2).Value = EbiPT1

it sets the value in that cell to zero, if you need that value in the
variable use

EbiPT1 = ActiveCell.Offset(0, 2).Value

also some values will only be set if the condition specified is met, as in
the Select Case statement.

 

[greg.moberg]

Thank you for your replies!

I tried both of your suggestions, but this is what happened: The
macro gave an output, but it gave the same output over and over. What
I wanted the macro to do was to perform the calculations only when a
variable for 2 is stored. So values for PT1 and PT2 would be
constantly reassigned. The macro would also have to be able to repeat
the process down the entire spreadsheet to find the other variables
for 2.

Thanks again,

Greg