Excel Macro Help: How do I select without un-selecting in a Loop?

Question

For i = 1 To 10000
If Range("A" & i) = "1" Then
Range("A" & i).Select
End If
Next i
Selection.Copy
Range("B1").Paste


I'm learning how use macro. Please don't give answer that is not related to macro

Answer 1

You have a logic error in your IF statement.

Apparently you intend to copy all of the 1's in column A and IF you detect a 1 your selecting it ...
Your If statment

IF Range("A" & i ) = 1 it would never become true as you are not looking at the Value property of the cell which is being selected by the range object.

IF Range("A" & i ).Value = 1 Then will detect the 1's in column A

To select multiple cells which are not adjacent
Range("A1, A3, A10").select Notice the format of the string containing the Cell ID's...

A way to copy is to use a range variable and set it to the cells you wish to copy. Then Call the Copy method and specify a destination range

Dim copyRange As Range

Set copyRange = Range("A1, A3,A10")
copyRange.Copy Destination:=Range("B1")

This code will copy the three cells into the adjacent column starting at row 1. (B1,B2 & B3 will have data copied into them)

Using your For/Next and If statement Idea you can lassemble a string array of Cell ID's seperated by comma. After the loop is complete you may then use the Range Copy method to copy those cells in to your adjacen column

Answer 2

Not exactly sure what you're trying to get done - but if you want to have a macro copy a range from A1:A1000 and paste beginning at B1 - here's the macro code that will get that done:

Dim iStart, iEnd As Integer
iStart = 1
iEnd = 10000
Range("A" & iStart & ":A" & iEnd).Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste

There's likely a limit to the number of cells you can select individually, but if you need to do it that way - here's the macro code to select 10 cells:

Dim i as Integer
Dim sSelect As String
For i = 1 To 10
If i = 1 Then
sSelect = "(A" & i & ","
ElseIf i > 1 And i < 10 Then
sSelect = sSelect & "A" & i & ","
Else
sSelect = sSelect & "A" & i & ")"
End If
Next i
Range(sSelect).Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste

Good luck!

Answer 3

Hi Ny

I work with advance Excel macros and formulas everyday, so i just need you to explain what exactly you need to accomplish, so we can give you a better approach...