Sheets syntax

[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

 

[Chip Pearson]

Application.Goto Worksheets("sheet1").Cells(2, 3), True


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

 

[Bobby]

Application.Goto Worksheets("sheet1").Cells(2, 3), True


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

Is there other ways? (To avoid the Goto that is) Regards.

 

[Don Guillett]

do you mean?

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

 

[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.

 

[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

 

[Bobby]

do you mean?

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

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

 

[Paul Lautman]

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

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

 

[Bobby]

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

Thank you very much!

 

[Bobby]

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

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.

 

[Bobby]

Please omit my subscript question. Found my error!
Regards