| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Storing variables in a macro and using those variables to performcalculations.
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Storing variables in a macro and using those variables to perform
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)) " wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Storing variables in a macro and using those variables to performcalculations.
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
| Using variables while running macro | Excel Programming | |||
| Excel-VB Macro Variables | Excel Programming | |||
| Storing and Retrieving Variables Question | Excel Programming | |||
| Storing Variables | Excel Programming | |||
Linear Mode
