PDA

View Full Version : [SOLVED] "For each ... next" loop not working



Marbo
03-11-2016, 04:33 AM
Hi,
New to VBA and new to this forum ... so bear with me

Excel file holding a dashboard based on an Addin to collect data from an external source and print the result (after calculation). This part of the macro works fine.
However, trying to "repeat" the process 17 times (17 different companies) with one macro seems to be a bridge to far for me.

"MyRange" holds all the company names, starting in cell G4 ending in cell G20, on sheet "Parameters".
"MyCells" to use all company names one by one, to be copied in a specific cell (G13) on sheet "DB Comxxx"

The macro executes loop nr 1 fine (this is reflecting cell G4 = first company), but gives error message when he needs to repeat the process on loop nr 2 (this would be reflecting cell G5 = second company)

Part of the code ...
~Sheets("Parameters").Select


Dim MyRange As Range
Dim MyCell As Range

Set MyRange = Worksheets("Parameters").Range("G4:G20")
For Each MyCell In MyRange


MyCell.Select ---> ERROR "run time error 1004 / Select Method of Range class failed
Selection.Copy
Sheets("DB_Comxxx").Select
Range("G13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

....

Next MyCell
'

End Sub

Any suggestions to solve this error ? :dunno

Thx,
Marc

BobUmlas
03-11-2016, 07:51 AM
When you run into that error, the Parameters sheet is not active, so you can't select the cell. Best solution is to NOT select it:
instead of
Mycell.Select
Selection.Copy

use
Mycell.Copy

Benzadeus
03-11-2016, 07:55 AM
Hello and welcome to VBAX. Please use code tags when pasting code.
Try using this code:

Dim MyRange As Range Dim MyCell As Range

Set MyRange = Worksheets("Parameters").Range("G4:G20")
For Each MyCell In MyRange
Worksheets("DB_Comxxx").Range("G13") = MyCell.Value2
'... code ...
DoEvents
Next MyCell