PDA

View Full Version : [SOLVED] Use Cell Value to go offset to another cell



Alephus
01-27-2016, 09:02 PM
Hello there

Here is what i have:
(everything is in same workbook and same sheet)

Cell D3 will have a value randomly set betwen 6 and 11. The objective is to use such value to find the corresponding cell in the E collum:

if the value is 6 for example cell E6 must be ActiveCell.

My problem is how to call the cell this way...

Now the why?! i have this:

Cell E5 has a starting value of 240 [E5=240-SUM(E6:E11)] , and i must distribute such value betwen cells E6 and E11 randomly by increments of 10 until E5 = 0. Also, no cell betwen E6 and E11 can have a value greater than 100

Here is the code i made:


Sub RandomDist()'
' RandomDist Macro
'


'
Dim B As Integer
B = 0



Range("E6:E11").Select
Selection.ClearContents
Do


Range("E3").Select
Selection.Copy
Range("D3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False



-> need to call cell E#




If ActiveCell.Value < 100 Then
B = ActiveCell.Value
ActiveCell.Value = B + 10
B = 0
Else
End If


Range("E5").Select


Loop Until ActiveCell.Value = 0


End Sub

Thanks for the support. Also, if not obvious im still a beginner in vba, so go easy on me xP

Alephus
01-27-2016, 09:47 PM
Well i kinda solved it myself :P

For future reference here is how i got it:


Missing code:


Range("E" & Range("D3").Text).Select


And here is the final product:


Sub RandomDist()
'
' RandomDist Macro
'


'
Dim B As Integer
B = 0



Range("E6:E11").Select
Selection.ClearContents


Do
Range("E3").Select
Selection.Copy
Range("D3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False



Range("E" & Range("D3").Text).Select
If ActiveCell.Value < 100 Then
B = ActiveCell.Value
ActiveCell.Value = B + 10
B = 0
Else
End If


Range("E5").Select


Loop Until ActiveCell.Value = 0


End Sub