Read values from an Excel Table?
 

Hello,

I currently have a module that I created that reads a text file, parses it,
saves it as a temporary workbook, processes it and then saves a final
version. Works great.

However, there are four codes that I need to look for in my processing.
These codes are hard coded into a For-Next Loop:

For x = 1 To EndRow
Cells(y, 1).Select
If Cells(y, 1).Value Like "174*" Or _
Cells(y, 1).Value Like "175*" Or _
Cells(y, 1).Value Like "172*" Or _
Cells(y, 1).Value Like "173*" Or _
Cells(y, 1).Value Like "399*" Then
y = y + 1
Else
Cells(y, 1).Select
Selection.Delete Shift:=xlUp
If ActiveCell.Row 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End If
Next


To reduce the number of times I need to touch the code, my team wants me to
create an Excel table and enter values into that table. I would then run the
code and process for values retrieved from the table. that way the code
stays in place and I can just add/delete values as needed.

Question: How would I read values into a FOR-NEXT loop from an Excel Table?
Do I create an array?

Any ideas would definitely be appreciated.

Thanks,

Tony

Read values from an Excel Table?
 

"Excel Table" is not very specific. Assuming it refers to a Range on a
worksheet other than the active one, you could do something like the
following. Such Range likely should not be on the ActiveSheet since
the code deletes rows.

Sub Test()
Dim bFlag As Boolean
Dim c As Range
Dim rng As Range

y = ActiveSheet.Range("A1").End(xlDown).Row
EndRow = y
Set rng = Sheets("Sheet2").Range("A1:A5")
For y = 1 To EndRow
Cells(y, 1).Select
bFlag = False
For Each c In rng
If Cells(y, 1).Value Like c Then
bFlag = True
End If
Next c
If bFlag = True Then
y = y + 1
Else
Cells(y, 1).Select
Selection.Delete Shift:=xlUp
If ActiveCell.Row 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End If
Next
End Sub

Hth,
Merjet

Read values from an Excel Table?
 

Assume your table of values starts in A1 of sheet Table1 in a workbook named
Data.xls and Data.xls is open

Dim r as Range, r1 as Range, v as Variant
Dim bFound as Boolean, j as Long
set r = workbooks("Data.xls").Worksheets("Table1").Range(" A1")
if isempty(r.offset(1,0)) then
redim v(1 to 1)
v(1) = r.value
Else
set r1 = r.parent.Range(r,r.End(xldown))
v = Application.Transpose(r1)
End if

For x = 1 to EndRow
bfound = False
for j = lbound(v) to ubound(v)
If Cells(y, 1).Value Like v(j) & "*" Then
bFound = True
exit for
end if
Next
if bFound then
cells(y,1).Select
y = y + 1
Else
Cells(y, 1).Select
Selection.Delete Shift:=xlUp
If ActiveCell.Row 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End If
Next


--
Regards,
Tom Ogilvy


"Webtechie" wrote:

Hello,

I currently have a module that I created that reads a text file, parses it,
saves it as a temporary workbook, processes it and then saves a final
version. Works great.

However, there are four codes that I need to look for in my processing.
These codes are hard coded into a For-Next Loop:

For x = 1 To EndRow
Cells(y, 1).Select
If Cells(y, 1).Value Like "174*" Or _
Cells(y, 1).Value Like "175*" Or _
Cells(y, 1).Value Like "172*" Or _
Cells(y, 1).Value Like "173*" Or _
Cells(y, 1).Value Like "399*" Then
y = y + 1
Else
Cells(y, 1).Select
Selection.Delete Shift:=xlUp
If ActiveCell.Row 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End If
Next


To reduce the number of times I need to touch the code, my team wants me to
create an Excel table and enter values into that table. I would then run the
code and process for values retrieved from the table. that way the code
stays in place and I can just add/delete values as needed.

Question: How would I read values into a FOR-NEXT loop from an Excel Table?
Do I create an array?

Any ideas would definitely be appreciated.

Thanks,

Tony

Read values from an Excel Table?
 

Perfect. On the mark! It worked perfectly.

Thanks!

Tony


"merjet" wrote:

"Excel Table" is not very specific. Assuming it refers to a Range on a
worksheet other than the active one, you could do something like the
following. Such Range likely should not be on the ActiveSheet since
the code deletes rows.

Sub Test()
Dim bFlag As Boolean
Dim c As Range
Dim rng As Range

y = ActiveSheet.Range("A1").End(xlDown).Row
EndRow = y
Set rng = Sheets("Sheet2").Range("A1:A5")
For y = 1 To EndRow
Cells(y, 1).Select
bFlag = False
For Each c In rng
If Cells(y, 1).Value Like c Then
bFlag = True
End If
Next c
If bFlag = True Then
y = y + 1
Else
Cells(y, 1).Select
Selection.Delete Shift:=xlUp
If ActiveCell.Row 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End If
Next
End Sub

Hth,
Merjet