Use a cell value to point to desired location.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

scenario 1: say, if cell A1 contains the value 10,2 and cell D1 contains
value 8.

scenario 2: say, if cell A1=10, cell B2=2 and D1=8.

how can I write a macro to select the cell J2 (ie. 10,2) and place the value
8 in it? ie. use the values as an X,Y coordinate. Which would be the more
efficient way of doing it?
 
GeneWan said:
scenario 1: say, if cell A1 contains the value 10,2 and cell D1 contains
value 8.

scenario 2: say, if cell A1=10, cell B2=2 and D1=8.

how can I write a macro to select the cell J2 (ie. 10,2) and place the value
8 in it? ie. use the values as an X,Y coordinate. Which would be the more
efficient way of doing it?
Click to expand...

See attached file: http://cjoint.com/?creRs6Unma
 
Hi Ardus,

I managed to locate the code under "Feuil1 (Feuil1)", as far as I would like
to, for now, my understanding is limited to codes run under "Modules". As I
am still familliarizing myself with VBA. Hence appreciate if you could
translate it under "Modules" if it's not too much trouble?
 
Hi Gene,

For scenario1, try:

'=============>>
Public Sub TesterA()
Dim Rng1 As range, rng2 As range
Dim arr As Variant

Set Rng1 = range("A1")
Set rng2 = range("D1")

arr = Split(Rng1.Value, ",")

Cells(CLng(arr(1)), CLng(arr(0))).Value = rng2.Value

End Sub
'<<=============

For the second scenario, try:

'=============>>
Public Sub TesterB()
Dim Rng1 As range, rng2 As range, rng3 As range
Dim i As Long, j As Long

Set Rng1 = range("A1")
Set rng2 = range("B2")
Set rng3 = range("D1")

i = Rng1.Value
j = rng2.Value

Cells(j, i).Value = rng3.Value

End Sub
'<<=============
 
you could use something like this

sub test
Cells(Range("B2").Value, Range("A1").Value).Value = Range("D1").Value
end sub
 
Thanks Norman!

it works well and now I am looking to do this over a range... might be back
for more!
 
Thanks Gary~

Sweet!

Gary Keramidas said:
you could use something like this

sub test
Cells(Range("B2").Value, Range("A1").Value).Value = Range("D1").Value
end sub
Click to expand...
 
GeneWan said:
scenario 1: say, if cell A1 contains the value 10,2 and cell D1
contains
value 8.

scenario 2: say, if cell A1=10, cell B2=2 and D1=8.

how can I write a macro to select the cell J2 (ie. 10,2) and place the
value
8 in it? ie. use the values as an X,Y coordinate. Which would be the
more
efficient way of doing it?
Click to expand...

scenario 1

ComPos=Instr(1,[a1],",") ' locate comma
X=Val(Left([a1],ComPos-1))
Y=Mid(([a1],ComPos+1,5))
Cells(Y,X)=[d1]

scenario 2

cells([b2],[[a1]).value=[d1]

A V Veerkar
 
Hi A,

Thanks, however, when I execute the line:
Y=Mid([a1],ComPos+1,5)

I encountered an "Type mismatched" error, any idea why?

avveerkar said:
scenario 1: say, if cell A1 contains the value 10,2 and cell D1
contains
value 8.

scenario 2: say, if cell A1=10, cell B2=2 and D1=8.

how can I write a macro to select the cell J2 (ie. 10,2) and place the
value
8 in it? ie. use the values as an X,Y coordinate. Which would be the
more
efficient way of doing it?
Click to expand...

scenario 1

ComPos=Instr(1,[a1],",") ' locate comma
X=Val(Left([a1],ComPos-1))
Y=Mid(([a1],ComPos+1,5))
Cells(Y,X)=[d1]

scenario 2

cells([b2],[[a1]).value=[d1]

A V Veerkar
Click to expand...
 
Hi A,

I've decided to go with scenario 1, hence, assuming a1=10,2 could I do the
following? I'm a true amatuer to this, really do appreciate if you could
advise on the correct arguments that I should use..

ComPos = InStr(1, Cells([a], ), ",")
x = Val(Left((Cells([a], )), ComPos - 1))
y = Mid((Cells([a, b])), ComPos + 1, 5)
Cells(y, x) = [f2]


avveerkar said:
scenario 1: say, if cell A1 contains the value 10,2 and cell D1
contains
value 8.

scenario 2: say, if cell A1=10, cell B2=2 and D1=8.

how can I write a macro to select the cell J2 (ie. 10,2) and place the
value
8 in it? ie. use the values as an X,Y coordinate. Which would be the
more
efficient way of doing it?
Click to expand...

scenario 1

ComPos=Instr(1,[a1],",") ' locate comma
X=Val(Left([a1],ComPos-1))
Y=Mid(([a1],ComPos+1,5))
Cells(Y,X)=[d1]

scenario 2

cells([b2],[[a1]).value=[d1]

A V Veerkar
Click to expand...
 
Back
Top