DMain
01-22-2015, 06:26 AM
Hello,
My problem is that I cannot effectively transfer data from a cell in sheet A to a formula in sheet B.
To quickly summarise my code, what I am attempting to do is set the inputs for my formulas. This is done by checking a list and setting the value based on a variable name from that list. If the variable is from another sheet I need to pull this location through so that if it changes, my formula answer will change. For example if the words "from page 230" appear, the worksheet should open sheet 230 and an input box should appear to ask me to select a cell containing the relevant variable. This cell location must be stored so that the value stored within it can be used within formulas later on.
This is my code so far:
Sub Simulate()
Dim SearchFor As String
Dim SheetName As String
Dim Check As Integer
Dim HoldWord As Variant
myValue = Range("A2").Text 'current sheet name
SearchFor = "From Page"
size = 5 'Location of the first input
For i = 1 To 10 'for number of textboxes
j = size + I
If InStr(Cells(j, 2), SearchFor) <> 0 Then 'check to see if input is from a different page
SheetName = Cells(j, 2).Text 'Sheet name of new location
Sheets(SheetName).Select 'Open new sheet location
Set IN1 = Application.InputBox("Choose the input from this page", , , , , , , 8) 'Ask user to choose relevant cell and save as IN1
Sheets(myValue).Select 'Return to original sheet
Cells(j, 3).Formula = "=" & "SheetName!IN1.Address" 'This does not work. I am attempting to store IN1 address in the current worksheet
Else
Cells(j, 3).Value = UserForm1.Controls("TextBox" & i).Value 'Set the values from the userform
End If
Next i
End Sub
Sorry for not having the code in the correct format - I am not sure how to get that box to appear.
I realize that my line for storing the variable isn't correct - If anyone can provide advice or help it would be greatly appreciated.
Thanks,
DMain
My problem is that I cannot effectively transfer data from a cell in sheet A to a formula in sheet B.
To quickly summarise my code, what I am attempting to do is set the inputs for my formulas. This is done by checking a list and setting the value based on a variable name from that list. If the variable is from another sheet I need to pull this location through so that if it changes, my formula answer will change. For example if the words "from page 230" appear, the worksheet should open sheet 230 and an input box should appear to ask me to select a cell containing the relevant variable. This cell location must be stored so that the value stored within it can be used within formulas later on.
This is my code so far:
Sub Simulate()
Dim SearchFor As String
Dim SheetName As String
Dim Check As Integer
Dim HoldWord As Variant
myValue = Range("A2").Text 'current sheet name
SearchFor = "From Page"
size = 5 'Location of the first input
For i = 1 To 10 'for number of textboxes
j = size + I
If InStr(Cells(j, 2), SearchFor) <> 0 Then 'check to see if input is from a different page
SheetName = Cells(j, 2).Text 'Sheet name of new location
Sheets(SheetName).Select 'Open new sheet location
Set IN1 = Application.InputBox("Choose the input from this page", , , , , , , 8) 'Ask user to choose relevant cell and save as IN1
Sheets(myValue).Select 'Return to original sheet
Cells(j, 3).Formula = "=" & "SheetName!IN1.Address" 'This does not work. I am attempting to store IN1 address in the current worksheet
Else
Cells(j, 3).Value = UserForm1.Controls("TextBox" & i).Value 'Set the values from the userform
End If
Next i
End Sub
Sorry for not having the code in the correct format - I am not sure how to get that box to appear.
I realize that my line for storing the variable isn't correct - If anyone can provide advice or help it would be greatly appreciated.
Thanks,
DMain