Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I read a range of scenario (input) values from a table? | Excel Discussion (Misc queries) | |||
Read Excel row values | Excel Worksheet Functions | |||
read the values from a excel row | Excel Worksheet Functions | |||
Excel-read a table | Excel Worksheet Functions | |||
Read EXCEL table | Excel Programming |