How do I use what is typed in a cell in programming

[MP999]

Sorry very new to VBA - I want to use what is in a cell in programming.
The cell should be a value 0 to 69 depending on the result I want the
computer to get different data from different worksheets. I have 10
worksheets each with 7 columns.
So if the Value is 15 I will replace Sheet 1!'$A with Sheet 3!'$B. I hope it
makes sense to you as I am beginning to lose it!

 

[MP999]

Thanks for taking time to reply but rather than display it I want to write
the code In lay persons terms:
If A1 on Sheet 1 = 12 then cell b1= sheet 3 e1
If A1 on Sheet 1 = 13 then cell b1 = Sheet 3 f1

 

[Jacob Skaria]

Please try and feedback. Launch VBE using Alt+F11. Insert module and paste
the below code. Save and close VBE. From menu Tools|Macro|Run Macro

Sub Macro()

With ActiveWorkbook
If .Sheets("Sheet1").Range("A1") = 12 Then
..Sheets("Sheet1").Range("B1") = .Sheets("Sheet3").Range("E1")
ElseIf .Sheets("Sheet1").Range("A1") = 13 Then
..Sheets("Sheet1").Range("B1") = .Sheets("Sheet3").Range("F1")
End If
End With

End Sub

If this post helps click Yes

 

[Gord Dibben]

Just to clarify.................

You have 70 possible inputs...........0 to 69

You have 10 sheets with 7 columns each sheet.

You want to have B1 on sheet1 equal one of 70 possible values from those 10
sheets and 7 columns.

Is there any logic behind each of the 70 possible results?

I probably can't help but just trying to get a picture.


Gord Dibben MS Excel MVP

 

[MP999]

Gord
It is 10 weeks of activities (Shift Pattern) and by using a formula to work
out the start of that 10 weeks it gives a number when I take that start away
from Today.
The supervisor can then see who is supposed to do what on that particular
day ie Reception Bldg A, Reception Bldg B etc but you have the rest spot on.
I did have A MACRO attached to buttons when the shift pattern was shorter
whereby they clicked "Mon Lates" or "Mon Nights" and it copied a duty sheet
and worked out that Mon lates was week 2 and would be the first column.
I used a select and replace part of the formula ie I set up the sheet for
Mon Week 1, if the button pressed was Wed Week 2 it would replace the formula
in all the cells by find and replace ie Week 2 instead of 1, column C instead
of column A
Hope it makes a little more sense now

 

[Jacob Skaria]

I really doubt whether the below piece of code helps...if it does well and
good...