Manipulate Data if Column contains a "Value" in macro

JohnHenry

New Member
Joined
Mar 12, 2013
Messages
27
Hello All,

I have a large inventory sheet. My macro currently sorts all of the data by many traits and converts necessary cells to number format and numeric values. I need it to copy and paste only certain data into another sheet. The code below works well when the "string" is present in the column. In my example, I want to copy all data that has exactly a "5" in the appropriate column (VehCol) (don't include data such as say "658"). However, since the sheet's data changes month to month, the particular vehicle may not appear on the sheet. If there is no "5" in the column, I receive a "Run-time error '1004': Application-defined or object-defined error." The macro needs to do this for roughly 20 some-what random vehicle numbers. Currently, I have simply copied and pasted the "Vehicle Sort" code below for each different vehicle number, as the vehicle numbers may change in the future. How do I compensate if there is a lack of a vehicle 5 in the data?

Thanks in advance for any feedback.


'''''''''''''''''''''''''''''''''''''''''''''''''''''''Relevant Code''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Discover location of "Vehicle" column
'Vehicle
Range("A1:Z100").Select
Cells.Find(What:="Vehicle").Select
VehCol = Selection.Column
VehRow = Selection.Row

'****************Vehicle Sort**************
'Vehicles: 5,8,9,20,24,33,39,43,55,75,76,77,83,84,85,86,509,510,511,752
'Vehicle Five
' Figure out where "Vehicle 5" data starts.
Sheets("Data").Select
Dim FiveRow As Long
Dim FiveStart As Long, FiveEnd As Long
For FiveRow = 3 To 65536
If Cells(FiveRow, VehCol).Value = "5" Then
FiveStart = FiveRow
Exit For
End If
Next FiveRow

' Figure out where the "Vehicle 5" data ends.
For FiveRow = FiveStart To 65536
If Cells(FiveRow, VehCol).Value <> "5" Then
FiveEnd = FiveRow
Exit For
End If
Next FiveRow
FiveEnd = FiveEnd - 1

'Paste data into correct month available cells
Sheets(Month).Select
OpenRow = 2
Do While Cells(OpenRow, "A") <> ""
OpenRow = OpenRow + 1
Loop
Sheets("Data").Select
Cells(FiveStart, "A").Select
Range(Selection, Cells(FiveEnd, "Z")).Copy
Sheets(Month).Select
Range("A" & OpenRow).PasteSpecial
Application.CutCopyMode = False

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Code End'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

JohnHenry

New Member
Joined
Mar 12, 2013
Messages
27
I forgot to note that part of the sorting function already sorts by vehicle number, so all of the vehicle "5" rows are adjacent to each other.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,371
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top