Sheets syntax

B

Bobby

Hi, I would like to write the following to point(refer to) directly to
the required cells in a one liner. What I wrote was:

Sheets("Sheet1").Select
Cells(2, 3).Select

Not very efficient, Any hints? Thank's ahead
 
D

Don Guillett

do you mean?

Sheets("Sheet1").Cells(2, 3)=1
myvalue=Sheets("Sheet1").Cells(2, 3)
 
D

Dave Peterson

Application.goto is different that the VBA GoTo (branching command).

If you've been taught to avoid GoTo's at any cost, this isn't the type of GoTo
that needs to be avoided.
 
C

Chip Pearson

If your point is to select the cell, then there is no alternative
to your original code and the code I provided. That said, it is
rare that you actually need to Select anything in Excel. Instead,
you can access the range directly:

Worksheets("Sheet1").Cells(2, 3).Value = 123


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
B

Bobby

Don said:
do you mean?

Sheets("Sheet1").Cells(2, 3)=1
myvalue=Sheets("Sheet1").Cells(2, 3)
Click to expand...
Ok here I go: What I am doing is to apply the cell filling color from
one Sheet1 Cells(2,3) to a correspondant Sheet2 Cells(3,2)
I do that with 2 loops assignation and at 252 Cells. It works ok but
my screen keeps on flashing for about 5 secondes. I would like to avoid
that! Here the code

Dim r, c, rM, cM As Integer
r = 3
c = 2
rM = 3
cM = 3

Do While cM < 15
Do While c < 23

Sheets("Dépenses").Select
Cells(r, c).Select

If Selection.Interior.ColorIndex = 6 Then

Sheets("DepensesM").Select
Cells(rM, cM).Select
Selection.Interior.ColorIndex = 6
Else
Selection.Interior.ColorIndex = 2
End If

c = c + 1
rM = rM + 1
Sheets("Depenses").Select
Loop

cM = cM + 1
rM = 3
r = r + 1
c = 2

Loop

Sheets("Depenses").Select
Range("B3").Select
Sheets("DepensesM").Select
Range("C3").Select

End Sub
 
P

Paul Lautman

Bobby said:
Ok here I go: What I am doing is to apply the cell filling color from
one Sheet1 Cells(2,3) to a correspondant Sheet2 Cells(3,2)
I do that with 2 loops assignation and at 252 Cells. It works ok but
my screen keeps on flashing for about 5 secondes. I would like to
avoid that! Here the code

Dim r, c, rM, cM As Integer
r = 3
c = 2
rM = 3
cM = 3
Click to expand...

As Chip said, you don't need to select a cell in order to operate on it.
Also to stop the flashing, turn screenupdating off:

Dim r, c, rM, cM As Integer
r = 3
c = 2
rM = 3
cM = 3

Application.ScreenUpdating = False

Do While cM < 15
Do While c < 23

If Sheets("Dépenses").Cells(r, c).Interior.ColorIndex = 6 Then
Sheets("DepensesM").Cells(rM, cM).Interior.ColorIndex = 6
Else
Sheets("DepensesM").Cells(rM, cM).Interior.ColorIndex = 2
End If

c = c + 1
rM = rM + 1
Loop

cM = cM + 1
rM = 3
r = r + 1
c = 2

Loop

Sheets("Depenses").Select
Range("B3").Select
Sheets("DepensesM").Select
Range("C3").Select

End Sub
 
B

Bobby

Paul said:
As Chip said, you don't need to select a cell in order to operate on it.
Also to stop the flashing, turn screenupdating off:

Dim r, c, rM, cM As Integer
r = 3
c = 2
rM = 3
cM = 3

Application.ScreenUpdating = False

Do While cM < 15
Do While c < 23

If Sheets("Dépenses").Cells(r, c).Interior.ColorIndex = 6 Then
Sheets("DepensesM").Cells(rM, cM).Interior.ColorIndex = 6
Else
Sheets("DepensesM").Cells(rM, cM).Interior.ColorIndex = 2
End If

c = c + 1
rM = rM + 1
Loop

cM = cM + 1
rM = 3
r = r + 1
c = 2

Loop

Sheets("Depenses").Select
Range("B3").Select
Sheets("DepensesM").Select
Range("C3").Select

End Sub
Click to expand...
Thank you very much!
 
B

Bobby

Paul said:
As Chip said, you don't need to select a cell in order to operate on it.
Also to stop the flashing, turn screenupdating off:

Dim r, c, rM, cM As Integer
r = 3
c = 2
rM = 3
cM = 3

Application.ScreenUpdating = False

Do While cM < 15
Do While c < 23

If Sheets("Dépenses").Cells(r, c).Interior.ColorIndex = 6 Then
Sheets("DepensesM").Cells(rM, cM).Interior.ColorIndex = 6
Else
Sheets("DepensesM").Cells(rM, cM).Interior.ColorIndex = 2
End If

c = c + 1
rM = rM + 1
Loop

cM = cM + 1
rM = 3
r = r + 1
c = 2

Loop

Sheets("Depenses").Select
Range("B3").Select
Sheets("DepensesM").Select
Range("C3").Select

End Sub
Click to expand...
Paul why do I get a subscript error if I do:
Sheets("DepensesM").Cells(rM, cM).Interior.ColorIndex =
Sheets("Dépenses").Cells(r, c).Interior.ColorIndex
Regards.
 
Top