davidplowman
New Member
- Joined
- Oct 31, 2013
- Messages
- 10
Hello:
I have an issue and I'm hoping someone can help me with.
I have a spreadsheet that's has 28 columns and more than 3,000 rows.
One of the columns is a year range (i.e. 2010-2013, 1988-1999, 1996-1998 or whatever).
The file has to be submitted to another department for an upload to a website, and each year has to be listed on a separate row, with all of the information remaining the same.
So in other words, a portion of my spreadsheet that looks like this:</SPAN>
<TBODY>
</TBODY>
Would ultimately look like this:</SPAN>
<TBODY>
</TBODY>Would ultimately look like this:</SPAN>
<TBODY>
</TBODY>
Someone suggested I try the following VBA program:</SPAN>
1. Sub SplitDataByYears()</SPAN>
2. Dim R As Long, C As Long, CC As Long, Index As Long, TotalYears As Long, LastRow As Long</SPAN>
3. Dim YearCol As Long, ArrIn As Variant, ArrOut As Variant</SPAN>
4. Const YearLetter As String = "f"</SPAN>
5. YearCol = Cells(1, YearLetter).Column</SPAN>
6. LastRow = Cells(Rows.Count, YearCol).End(xlUp).Row</SPAN>
7. ArrIn = Range("A1:AB" & LastRow)</SPAN>
8. TotalYears = 1 + Evaluate("SUMPRODUCT(ISNUMBER(FIND(""-""," & YearLetter & "2:" & YearLetter & _</SPAN>
9. LastRow & "))*(1+RIGHT(" & YearLetter & "2:" & YearLetter & LastRow & _</SPAN>
10. ",4)-LEFT(" & YearLetter & "2:" & YearLetter & LastRow & ",4)))")</SPAN>
11. ReDim ArrOut(1 To TotalYears, 1 To 28)</SPAN>
12. For R = 2 To UBound(ArrIn)</SPAN>
13. For CC = CLng(Left(ArrIn(R, YearCol), 4)) To CLng(Right(ArrIn(R, YearCol), 4))</SPAN>
14. Index = Index + 1</SPAN>
15. For C = 1 To 28</SPAN>
16. If C = YearCol Then</SPAN>
17. ArrOut(Index, C) = CC</SPAN>
18. Else</SPAN>
19. ArrOut(Index, C) = ArrIn(R, C)</SPAN>
20. End If</SPAN>
21. Next</SPAN>
22. Next</SPAN>
23. Next</SPAN>
24. With Sheets("Sheet2")</SPAN>
25. .Range("A1:AB1") = ActiveSheet.Range("A1:AB1").Value</SPAN>
26. .Range("A2:AB" & UBound(ArrOut) + 1) = ArrOut</SPAN>
27. End With</SPAN>
Unfortunately, I’ve never used VBA, and can’t seem to get off the ground on this. I receive an error that reads “Compile error: Expected line number or statement or end of statement.”</SPAN>
Could anyone help me decode this error and advise how I can fix it?</SPAN>
Also, could someone guide me to a good beginners guide to VBA, all of this is so new to me that I don’t know where to begin troubleshooting something when I get an error.</SPAN>
Thank you,</SPAN>
David</SPAN>
I have an issue and I'm hoping someone can help me with.
I have a spreadsheet that's has 28 columns and more than 3,000 rows.
One of the columns is a year range (i.e. 2010-2013, 1988-1999, 1996-1998 or whatever).
The file has to be submitted to another department for an upload to a website, and each year has to be listed on a separate row, with all of the information remaining the same.
So in other words, a portion of my spreadsheet that looks like this:</SPAN>
Source</SPAN> | New PN (Main # to Search By)</SPAN> | Make</SPAN> | Vehicle Type</SPAN> | Year (From - To)</SPAN> | Model</SPAN> | Engine</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2010-2013</SPAN> | DB</SPAN> | FE</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2012-2013</SPAN> | DB</SPAN> | HA</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 1999-2003</SPAN> | Various Models</SPAN> | FE</SPAN> |
<TBODY>
</TBODY>
Would ultimately look like this:</SPAN>
Source</SPAN> | New PN (Main # to Search By)</SPAN> | Make</SPAN> | Vehicle Type</SPAN> | Year (From - To)</SPAN> | Model</SPAN> | Engine</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2010</SPAN> | DB</SPAN> | FE</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2011</SPAN> | DB</SPAN> | FE</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2013</SPAN> | DB</SPAN> | FE</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2012</SPAN> | DB</SPAN> | HA</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2013</SPAN> | DB</SPAN> | HA</SPAN> |
<TBODY>
</TBODY>
Source</SPAN> | New PN (Main # to Search By)</SPAN> | Make</SPAN> | Vehicle Type</SPAN> | Year (From - To)</SPAN> | Model</SPAN> | Engine</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2010</SPAN> | DB</SPAN> | FE</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2011</SPAN> | DB</SPAN> | FE</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2013</SPAN> | DB</SPAN> | FE</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2012</SPAN> | DB</SPAN> | HA</SPAN> |
Lester</SPAN> | A7T03277A</SPAN> | Hyster</SPAN> | Lift Truck</SPAN> | 2013</SPAN> | DB</SPAN> | HA</SPAN> |
<TBODY>
</TBODY>
Someone suggested I try the following VBA program:</SPAN>
1. Sub SplitDataByYears()</SPAN>
2. Dim R As Long, C As Long, CC As Long, Index As Long, TotalYears As Long, LastRow As Long</SPAN>
3. Dim YearCol As Long, ArrIn As Variant, ArrOut As Variant</SPAN>
4. Const YearLetter As String = "f"</SPAN>
5. YearCol = Cells(1, YearLetter).Column</SPAN>
6. LastRow = Cells(Rows.Count, YearCol).End(xlUp).Row</SPAN>
7. ArrIn = Range("A1:AB" & LastRow)</SPAN>
8. TotalYears = 1 + Evaluate("SUMPRODUCT(ISNUMBER(FIND(""-""," & YearLetter & "2:" & YearLetter & _</SPAN>
9. LastRow & "))*(1+RIGHT(" & YearLetter & "2:" & YearLetter & LastRow & _</SPAN>
10. ",4)-LEFT(" & YearLetter & "2:" & YearLetter & LastRow & ",4)))")</SPAN>
11. ReDim ArrOut(1 To TotalYears, 1 To 28)</SPAN>
12. For R = 2 To UBound(ArrIn)</SPAN>
13. For CC = CLng(Left(ArrIn(R, YearCol), 4)) To CLng(Right(ArrIn(R, YearCol), 4))</SPAN>
14. Index = Index + 1</SPAN>
15. For C = 1 To 28</SPAN>
16. If C = YearCol Then</SPAN>
17. ArrOut(Index, C) = CC</SPAN>
18. Else</SPAN>
19. ArrOut(Index, C) = ArrIn(R, C)</SPAN>
20. End If</SPAN>
21. Next</SPAN>
22. Next</SPAN>
23. Next</SPAN>
24. With Sheets("Sheet2")</SPAN>
25. .Range("A1:AB1") = ActiveSheet.Range("A1:AB1").Value</SPAN>
26. .Range("A2:AB" & UBound(ArrOut) + 1) = ArrOut</SPAN>
27. End With</SPAN>
Unfortunately, I’ve never used VBA, and can’t seem to get off the ground on this. I receive an error that reads “Compile error: Expected line number or statement or end of statement.”</SPAN>
Could anyone help me decode this error and advise how I can fix it?</SPAN>
Also, could someone guide me to a good beginners guide to VBA, all of this is so new to me that I don’t know where to begin troubleshooting something when I get an error.</SPAN>
Thank you,</SPAN>
David</SPAN>