How to cycle through different cells in a for loop?

[The Teej]

Hi guys.

 

I'm working on a spreadsheet for work and using VBA to generate data (as well as theme the data cells) but I'm noticing I'm running into a lot of copypasta over redundant commands which in my programming experience should actually be done in a for loop - for example, the following segment of code:

'All Columns
 Range("A2:A134").BorderAround xlContinuous
 Range("B2:B134").BorderAround xlContinuous
 Range("C2:C134").BorderAround xlContinuous
 Range("D2:D134").BorderAround xlContinuous
 Range("E2:E134").BorderAround xlContinuous
 Range("F2:F134").BorderAround xlContinuous
 Range("G2:G134").BorderAround xlContinuous
 Range("H2:H134").BorderAround xlContinuous
 Range("I2:I134").BorderAround xlContinuous
 Range("J2:J134").BorderAround xlContinuous
 Range("K2:K134").BorderAround xlContinuous
 Range("L2:L134").BorderAround xlContinuous
 Range("M2:M134").BorderAround xlContinuous
 Range("N2:N134").BorderAround xlContinuous

Now, the code works, but it looks a bit messy. As you can see, the majority of this code in this segment is pretty much repeated - and it's not the only part of the code where this is taking place - and it's making small ammendments to the code quite a time consuming process - simply because of all of the copy and pasting that's going on. So in the spirit of time saving and cleaner code, is there a way I can cycle through columns in a for loop? I'm aware of how to do it for rows, as long as it's in the same column - but seen as columns are not numerical, I wasn't sure how to apply the same process for columns.

 

I'm pretty new to VBA, and haven't dabbled in VB in nearly 10 years, so I'm pretty rusty! Any help would be appreciated. Thanks :)

[Andre S. Veteran]

I'm not familiar with VBA whatsoever, but in pseudo-code you basically want:

for letter = "A" to "Z" do
    Range(letter + "2:" + letter + "134").BorderAround xlContinuous

[Bertch]

Something like this should work for you

For i = 1 to 100
    Columns(i).BorderAround xlContinuous
Next i

[The Teej]

 

I'm not familiar with VBA whatsoever, but in pseudo-code you basically want:

for letter = "A" to "Z" do
    Range(letter + "2:" + letter + "134").BorderAround xlContinuous

 

Unfortunately the range function doesn't allow for string concatenation in the way you're describing, I've kind of tried this already I believe :(

 

 

Something like this should work for you

For i = 1 to 100
    Columns(i).BorderAround xlContinuous
Next i

 

No, because that would just select the whole column and put an outline border on it - what I want is a set number of rows have an outline border, across a set number of columns.

[Andre S. Veteran]

Unfortunately the range function doesn't allow for string concatenation in the way you're describing, I've kind of tried this already I believe :(

As I said, this was just pseudo code, you need to figure out the syntax for string concatenation in VBA as I'm not familiar with it. Google is your friend when it comes to basic syntax questions. By the way it has nothing to do with the Range function in particular, which doesn't care how to string you pass to it was created.

[ramesees]

Isnt the string concatenation operator in VB the '&' symbol rather than '+' ?

[Haggis Veteran]

try this

 

this will find all cells that have data and border them

 

if its not exactly what you need you should be able to adjust it

Sub addborder()
'Determine extent of data in worksheet
LastRow = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column
FirstRow = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row
FirstColumn = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Column
'Select the range with data
Range(Cells(FirstRow, FirstColumn), Cells(LastRow, LastColumn)).Select
'Apply the borders
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End Sub 

[Dav-id]

Sub Macro1()
 On Error Resume Next
 Dim current As String
 Dim target As String

 For i = 3 To 9 ' go from first cell to row
    
    current = "c" & i ' cell counter
    Range("h" & i).Value = ""
    Range("i" & i).Value = ""
    Range("j" & i).Value = ""
    For j = 1 To 6
    
        If Cells(i, j).Interior.ColorIndex = Range("a1").Interior.ColorIndex Then ' if it says add then we...
           Range("h" & i).Value = Range("h" & i).Value & " " & Cells(i, j).Value
        End If
        If Cells(i, j).Interior.ColorIndex = Range("b1").Interior.ColorIndex Then ' if it says add then we...
           Range("i" & i).Value = Range("i" & i).Value & " " & Cells(i, j).Value
        End If
        If Cells(i, j).Interior.ColorIndex = Range("c1").Interior.ColorIndex Then ' if it says add then we...
           Range("j" & i).Value = Range("j" & i).Value & " " & Cells(i, j).Value
        End If
    Next j

 Next i ' loop and check the next cell
 End Sub

This is code that goes row by row checking cell by cell for a cell that matches the background colour of cell A1, A2 or A3 and concatenate the result to the right.

 

 

Attached is an image showing how you setup the sheet to get this code working..