Excel vb problem

[EnemyGateIsDown]

Hi

I am trying to modify the data in a cell using vb that I want to refer to by index eg: (10,1) rather than J1. Can any one tell me what the method is that I need to use to achieve this?

eg: mysheet.some_method(1,10) = 'Fred'

Help as always is greatly appreciated.

Cheers,

Chris

 

[xlbo]

Chris - you should really ask this in the VBA forumj but being as I'm here as well, the answer is to use the CELLS method

sheets("sheetname&quot.cells(row,column).value = "Fred"

If you want to refer to multiple cells at the same time, use Range AND Cells

Range(cells(row,col),cells(row,col)).value = "Fred"

would be the equivalent of something like range("A1:A10&quot.value = "Fred"

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[KenWright]

Worksheets("Sheet1&quot.Cells(10, 1) = "fred"

From here you can obviously then use other calculations to set the row/column number, which is useful if looping etc eg (Forget any logic as to what it is doing as it has just been snipped from another pice of code - Simply examples):-

Last used row:-
LstRow = Cells(Rows.Count, "A&quot.End(xlUp).Row

or

Misc uses
i = Cells(Rows.Count, "B&quot.End(xlUp).Row
j = Cells(Rows.Count, "B&quot.End(xlUp).Row - 2

Set Rng = Range("I3:Z" & i)
For k = 0 To 5
Rng.Copy Range("D" & ((j * k) + i) + 1)
Next k


'Bring Down the US(A) Lab Hours
Set Rng = Range("B3:B" & Cells(Rows.Count, "B&quot.End(xlUp).Row)
Rng.Copy Range("B" & (i + (j * 0) + 1))
Range("A" & (i + 1) & ":A" & (i + j)) = "A Lab"

'Bring Down the US(B) Lab Hours
Set Rng = Range("C3:C" & i)
Rng.Copy Range("B" & ((i + (j * 1)) + 1))
Range("A" & (i + (j * 1) + 1) & ":A" & (i + (j * 2))) = "B Lab"

'Bring Down the UK(A) Lab Hours
Set Rng = Range("D3" & i)
Rng.Copy Range("B" & ((i + (j * 2)) + 1))
Range("A" & (i + (j * 2) + 1) & ":A" & (i + (j * 3))) = "C Lab"


Regards
Ken...............

 

[EnemyGateIsDown]

Thanks for the help chaps.. Thats exactly what Im doing .. looping through some cells updating some values referencing by index.. but not being familiar with the Excel object hierachy was having some fun ..

Cheers,

Chris

 

[EnemyGateIsDown]

Hmmm... using the method doesnt seem to actually update the cell.. is there anything else I need to do in order to update the cell?

Eg: in Access I would have to include an rs.update to actually write the data back?

 

[xlbo]

nope - the line
cells(1,1).value = 10
will place the number 10 in cell A1 of the active sheet
sheets("Sheet1&quot.cells(1,1).value = 10
will put the value 10 in cell A1 of sheet1

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[chrisdq]

EXCEL Could someone show me how to add text to a listbox from VB?? THANKS.

 

[xlbo]

chrisdq
a: start your own thread
b: start the thread in the VBA forum
c: provide more info - where is the data coming from? - is the listbox on a worksheet or a userform ??

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde