Issues with macro that uses absolute and relative references

[schulthe]

I have a sheet that requires one user input value and then, based on a series of calculations, returns a value.

The user entered input value is cell B5. The calculated value is cell B23.

I'm trying to create a macro that passes a value to B5 from a different cell and then copies and pastes the outputted value of B23 to a different cell. For example, using the table below, I'm trying to right a macro to paste and copy the 1 into B5 and then copy and paste the calculated value of B23 into the neighboring cell. I will then use this macro to do the same thing for the values 2 and 3 in the table.


[TABLE="width: 250"]
<tbody>[TR]
[TD]5
[/TD]
[TD][/TD]
[TD]Input Value
[/TD]
[/TR]
[TR]
[TD]....
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD][/TD]
[TD]Calculated Vale
[/TD]
[/TR]
[TR]
[TD]...
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B5 value
[/TD]
[TD]B23 value
[/TD]
[/TR]
[TR]
[TD]135
[/TD]
[TD]1
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]136
[/TD]
[TD]2
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]137
[/TD]
[TD]3
[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]


I've tried to create a macro to do this. The problem is that when I try to use it to get the appropriate value for the input 2 it inputs the correct value into B5 but then cuts and pastes the calculated value, B23, into the cell for the value of 1. Below is the VBA code for the macro. Any help would be greatly appreciated! It may be worth noting that B5 and B23 and merged cells over columns B and C.

Sub Macro3()
'
' Macro3 Macro
'

'
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-165
Range("B5:C5").Select
ActiveSheet.Paste
Range("B23").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=117
ActiveCell.Offset(112, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-240
Range("C23").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=120
ActiveCell.Offset(112, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, -1).Range("A1:B1").Select
Application.CutCopyMode = False
Selection.Style = "Normal"
ActiveCell.Select
Selection.Style = "Normal"
ActiveCell.Offset(0, 1).Range("A1").Select
End Sub

 

[par60056]

This is a hard way to do this. Your macro is relative to ActiveCell but I have no idea which cell is active. I am going to assume that the 1 is in B135, 2 is in B136 ..., that you want the result in C135, C136, ... and that C135 is the active cell. Fell free to correct my assumptions and we can tune up the code

Sub Macro3()


' Put the value in B5
Cells(5, 2) = ActiveCell.Offset(0, -1).Value


'get the result from B23 and put it in the active cell
ActiveCell.Value = Cells(23, 2).Value


ActiveCell.Offset(1, 0).Activate
End Sub

 

[schulthe]

Thank you!!!!

 

[Fluff]

If B23 is being calculated by a formula, can't you just put that formula into the cells with the ? ?