Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array or InStr Difficulty
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. Im 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 Im 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array or InStr Difficulty
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 -- Regards, Tom Ogilvy "Arturo" wrote: 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. Im 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 Im 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
InStr | Excel Programming | |||
InStr | Excel Programming | |||
InStr and ADO | Excel Programming | |||
InStr used in SQL query | Excel Programming |