PDA

View Full Version : [SOLVED] VBA Linking cells within different sheets



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

p45cal
01-22-2015, 11:56 AM
instead of:
Cells(j, 3).Formula = "=" & "SheetName!IN1.Address" 'This does not work. I am attempting to store IN1 address in the current worksheet
try (you won't need the SheetName variable):
Cells(j, 3).Formula = "=" & IN1.Address(external:=True)
or:
Cells(j, 3).Formula = "='" & SheetName & "'!" & IN1.Address

completely untested.

DMain
01-23-2015, 01:15 AM
I really appreciate your help p45cal. I tested both and they work. Thanks!