I
Izran
The title pretty much state's my basic problem, but it's a hair more
complicated than that. I've got to filter through a fairly large
spreadsheet (Air_List) and find all rows that contain a value in a
specific column. After finding said rows, I gotta find all the columns
in that row that contain data. All this found data then needs to be
placed on another sheet (Commodity_Entry). The presentation of the
data differs too, as I will then take the row values and present them
in a column.
Example:
The Sheet with the cells to be transferred from:
Name data 1 data 2 data 3
ann 72 86 22
Bill 44 93
Jan 82
The Sheet with the cells to be transferred to:
Ann
72
86
22
Bill
44
93
Jan
82
Now I will warn you that I have just started writing VBA, but am fairly
proficient in C and C++. Here is the code I've developed to try and
accomplish this.
Sub Air_Service_View()
Dim iRowCountFrom As Integer, iColCountFrom As Integer
Dim iRowCountTo As Integer, iColCountTo As Integer
Dim iColumnCountMMM As Integer
Dim rFromCell As Range
Dim rToCell As Range
Dim iListMax As Integer
iRowCountTo = 10
iRowCountTo = 4
iColumnCountTo = 2
iColumnCountFrom = 2
iListMax = Sheets("Air_List").Range("A1")
'Sheets("Air_List").Range("A1")Contains the number of used cells in
that row
For iRowCountFrom = 4 To iListMax + 3
If Not IsNull(Sheets("Air_List").Cells(iRowCountFrom, 2).Text)
Then
Sheets("Commodity_Entry").Cells(iRowCountTo, 2).Text =
Sheets("Air_List").Cells(iRowCountFrom, 2).Text
'find MMMs
For iColCountMMM = 4 To 31
If Not
IsNull(Sheets("Air_List").Cells(iRowCountFrom, iColCountMMM).Text) Then
iRowCountTo = iRowCountTo + 1
Sheets("Commodity_Entry").Cells(iRowCountTo,
2).Text = _
Sheets("Air_List").Cells(iRowCountFrom,
iColCountMMM).Text
End If
Next iColCountMMM
End If
Next iRowCountFrom
End Sub
The big problem I'm having is that it's not recognizing the cells that
I am trying to transfer to. I keep getting a Subscript out of Range
error with them, but ironically it does recoginze the cells I am
transferring from. Of note is that I am starting at cell B4 in the
from sheet (Column B has a value if anything in that row has a value),
and transferring to the Commodity_Entry sheet starting at cell B10. I
am trying to not get too specific in naming as I need to apply this
algarythm to 20+ other databases.
Regards,
Michael P Manzi
complicated than that. I've got to filter through a fairly large
spreadsheet (Air_List) and find all rows that contain a value in a
specific column. After finding said rows, I gotta find all the columns
in that row that contain data. All this found data then needs to be
placed on another sheet (Commodity_Entry). The presentation of the
data differs too, as I will then take the row values and present them
in a column.
Example:
The Sheet with the cells to be transferred from:
Name data 1 data 2 data 3
ann 72 86 22
Bill 44 93
Jan 82
The Sheet with the cells to be transferred to:
Ann
72
86
22
Bill
44
93
Jan
82
Now I will warn you that I have just started writing VBA, but am fairly
proficient in C and C++. Here is the code I've developed to try and
accomplish this.
Sub Air_Service_View()
Dim iRowCountFrom As Integer, iColCountFrom As Integer
Dim iRowCountTo As Integer, iColCountTo As Integer
Dim iColumnCountMMM As Integer
Dim rFromCell As Range
Dim rToCell As Range
Dim iListMax As Integer
iRowCountTo = 10
iRowCountTo = 4
iColumnCountTo = 2
iColumnCountFrom = 2
iListMax = Sheets("Air_List").Range("A1")
'Sheets("Air_List").Range("A1")Contains the number of used cells in
that row
For iRowCountFrom = 4 To iListMax + 3
If Not IsNull(Sheets("Air_List").Cells(iRowCountFrom, 2).Text)
Then
Sheets("Commodity_Entry").Cells(iRowCountTo, 2).Text =
Sheets("Air_List").Cells(iRowCountFrom, 2).Text
'find MMMs
For iColCountMMM = 4 To 31
If Not
IsNull(Sheets("Air_List").Cells(iRowCountFrom, iColCountMMM).Text) Then
iRowCountTo = iRowCountTo + 1
Sheets("Commodity_Entry").Cells(iRowCountTo,
2).Text = _
Sheets("Air_List").Cells(iRowCountFrom,
iColCountMMM).Text
End If
Next iColCountMMM
End If
Next iRowCountFrom
End Sub
The big problem I'm having is that it's not recognizing the cells that
I am trying to transfer to. I keep getting a Subscript out of Range
error with them, but ironically it does recoginze the cells I am
transferring from. Of note is that I am starting at cell B4 in the
from sheet (Column B has a value if anything in that row has a value),
and transferring to the Commodity_Entry sheet starting at cell B10. I
am trying to not get too specific in naming as I need to apply this
algarythm to 20+ other databases.
Regards,
Michael P Manzi