Going to a cell reference

[Duckmorph]

Im trying to workout the cell refrence and then go to a cell a couple over and enter data

So far i have

x = Sheet4.Range("A1").formula

'which returns x as =Markets!F155

Now i have the problem, i want to use the row number (155) and go to column "Q" in "Markets" and enter some data but i cant seem to get there. I have been trying to get a split to work but with no success.

Any ideas?

 

[wigi]

Hello

This subtle piece of coding is all you need:

Sub Wigi()

    With Range(Mid(Sheet4.Cells(1).Formula, 2))
        .Offset(, 17 - .Column) = "new value"
    End With

End Sub

 

[Duckmorph]

Thanks Wigi for you reply,

Im getting an error still, maybe i didnt explain very well before.

Im trying to look at the formula in a cell, lets say......

Sheet4.Range("I7").Formula

This formula will return this "=Markets!F155"

And what im after doing is going to the "Q155" in the "Markets" sheet which is Sheet24

Then Inputing a new value.

Is that any better?

Cheers

 

[wigi]

Thanks Wigi for you reply,

Im getting an error still, maybe i didnt explain very well before.

Im trying to look at the formula in a cell, lets say......

Sheet4.Range("I7").Formula

This formula will return this "=Markets!F155"

And what im after doing is going to the "Q155" in the "Markets" sheet which is Sheet24

Then Inputing a new value.

Is that any better?

Cheers

What my code does is exactly that...
It will put the text "New value" in the cell in Q155 on sheet Markets.
Did you try it? I set it up on my PC exactly as you described it, and tested it, so I'm quite sure this works
What is the error you still get? On what line of code?

 

[Duckmorph]

Hey

Sorry Wigi yes this does work, my fault i'm new to this and should of posted originally exactly what i'm trying to do,

Im using a variable as well to locate the inital cell

the cell i want to access is

Dim prow As Integer

prow = MatchBox.Text + 4

With Range(Mid(Sheet4.Cells(prow, 9).Formula, 2))
.Offset(, 17 - .Column) = "New Value"
End With

But i get error in the red.

Any idea?

Cheers

 

[Duckmorph]

Ive narrowed it down a bit.

The problem im having is with the With Range(Mid(Sheet4.Cells(prow, 9).Formula, 2))

This is ok if its Range(Mid(Sheet4.Cells(1).Formula, 2)), but i need to be using the variable.

Cheers

 

[wigi]

Try...

prow = CLng(MatchBox.Text) + 4
With Range(Mid(Sheet4.Cells(prow, 9).Formula, 2))

 

[Duckmorph]

Hey

Im still getting the error, i cant seem to assign a variable in this spot, is the problem how im declaring the variable?

In this case prow = 7

so if i use With Range(Mid(Sheet4.Cells(7, 9).Formula, 2)) i still get an error

 

[wigi]

What does this:

Debug.Print Sheet4.Cells(7, 9).Formula

give you in the Immediate Window?

 

[Duckmorph]

I dont get anything....

if i replace the (1) with (7,9) or (prow, 9) i get a Run time error 1004, Method 'Range" of obeject'_Global" failed.

if i replace the (1) with a (2) the code works from "b1" but how come i cant input a row number in?