Array or InStr Difficulty

A

Arturo

I have a block of code that currently runs down one column (D), looping
through rows based on a count I pull from the data set. I need this code to
run down several discontinuous columns. I’m not familiar enough with arrays
or InStr to have this code run down columns (F [6], H [8], J [10, etc…). My
code pulls the number of rows and columns in the data set but I’m finding it
confusing as how to loop through those discontinuous columns, 12 including
column (D). If I could be pointed in the right direction – that would be
greatly appreciated.
Sincerely,
Arturo

Sub Reverse()
Dim myRange As Range
Dim rO As Integer
Dim coL As Integer
Dim LoopCount_C As Integer
Dim LoopCount_R As Integer
Dim VarA As Variant
Dim VarB As Variant


''' Rows("1:9").Delete Shift:=xlUp
Range("A1").Select
Application.ScreenUpdating = False
Set myRange = ActiveCell.CurrentRegion
rO = myRange.Rows.Count - 1
coL = myRange.Columns.Count
' Q3- SQ1
Range("D2").Select
''' Array Needed Here.
For LoopCount_R = 1 To rO
VarA = ActiveCell.Value
If VarA = 8 Or VarA = "" Then
VarA = ""
Else: VarA = 8 - VarA
End If
ActiveCell.Value = VarA
ActiveCell.Offset(1, 0).Select
Next
''' Range("A1").Select
Application.ScreenUpdating = True
End Sub
 
T

Tom Ogilvy

Sub Reverse()
Dim myRange As Range
Dim rO As Integer
Dim coL As Integer
Dim LoopCount_C As Integer
Dim LoopCount_R As Integer
Dim VarA As Variant
Dim VarB As Variant
Dim v as Variant

''' Rows("1:9").Delete Shift:=xlUp
Range("A1").Select
Application.ScreenUpdating = False
Set myRange = ActiveCell.CurrentRegion
rO = myRange.Rows.Count - 1
coL = myRange.Columns.Count
' Q3- SQ1
Range("D2").Select
v = Array(6,8,10,12,20,28,40,...,200)
for i = 0 to ubound(v)
For LoopCount_R = 1 To rO
cells(LoopCount_R,v(i)).Select
VarA = ActiveCell.Value
If VarA = 8 Or VarA = "" Then
VarA = ""
Else: VarA = 8 - VarA
End If
ActiveCell.Value = VarA
Next LoopCount_R
Next i
''' Range("A1").Select
Application.ScreenUpdating = True
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top