Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by SQLSearcher
at 2024-07-23 01:04:20
ModifyPoint:300 Replies:6 POST_ID:829079USER_ID:11980
Topic:
Spreadsheet Software;Microsoft Office Suite;Microsoft Excel Spreadsheet Software
Hello Experts Exchange
I have some code for Excel that will create one record from the data I have attached.
The code works if I have the table of data on row 1 and starting in column A, how do I change the code so that it works on row 7 like the example I have attached?
This is the code;
I have some code for Excel that will create one record from the data I have attached.
The code works if I have the table of data on row 1 and starting in column A, how do I change the code so that it works on row 7 like the example I have attached?
This is the code;
Sub Normalize() Dim SourceRange As Range Dim SourceArr As Variant Dim r As Long, c As Long Dim DestR As Long ' Update as needed Const StartDate As Date = #6/1/2014# ' Update as needed Set SourceRange = ActiveSheet.Range("a1:ak13") SourceArr = SourceRange.Value Worksheets.Add Range("a1:f1").Value = Array("Die", "Machine", "Part Desc", "Part No", "Date", "Units") DestR = 2 For r = 4 To UBound(SourceArr, 1) For c = 7 To UBound(SourceArr, 2) If SourceArr(r, c) <> "" Then Cells(DestR, 1) = SourceArr(r, 1) Cells(DestR, 2) = SourceArr(r, 2) Cells(DestR, 3) = SourceArr(r, 3) Cells(DestR, 4) = SourceArr(r, 4) Cells(DestR, 5) = StartDate + SourceArr(1, c) - 1 Cells(DestR, 6) = SourceArr(r, c) DestR = DestR + 1 End If Next Next MsgBox "Done"End Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:
Regards
SQLSearcher
Attachment:Losses-Example2.xls
Assisted Solution
Expert: Glenn Ray replied at 2024-07-23 11:52:47
50 points EXCELLENT
SQLSearcher,
RE: skipping zero values
Change line 26 of Patrick's code to
RE: skipping zero values
Change line 26 of Patrick's code to
Regards,
-Glenn
Author: SQLSearcher replied at 2024-07-23 06:09:50
Hello Patrick
What change do I have to make to the code if I want it to skip cells that have a zero in them?
Regards
SQLSearcher
What change do I have to make to the code if I want it to skip cells that have a zero in them?
Regards
SQLSearcher
Accepted Solution
Expert: Patrick Matthews replied at 2024-07-23 04:25:40
250 points EXCELLENT
This changes things a bit, using constants to define where to find the data. Update the constants as needed.
Sub Normalize() Dim SourceRange As Range Dim SourceArr As Variant Dim r As Long, c As Long Dim DestR As Long ' Update as needed Const StartDate As Date = #6/1/2014# Const LastRow As Long = 19 Const DateOffsetRow As Long = 7 Const DataStartRow As Long = 10 Set SourceRange = ActiveSheet.Range("a1:ak" & LastRow) SourceArr = SourceRange.Value Worksheets.Add Range("a1:f1").Value = Array("Die", "Machine", "Part Desc", "Part No", "Date", "Units") DestR = 2 For r = DataStartRow To LastRow For c = 7 To UBound(SourceArr, 2) If SourceArr(r, c) <> "" Then Cells(DestR, 1) = SourceArr(r, 1) Cells(DestR, 2) = SourceArr(r, 2) Cells(DestR, 3) = SourceArr(r, 3) Cells(DestR, 4) = SourceArr(r, 4) Cells(DestR, 5) = StartDate + SourceArr(DateOffsetRow, c) - 1 Cells(DestR, 6) = SourceArr(r, c) DestR = DestR + 1 End If Next Next MsgBox "Done"End Sub 1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:
Expert: duncanb7 replied at 2024-07-23 03:37:25
What Excel 2003 or 2007 are you using ?
Author: SQLSearcher replied at 2024-07-23 03:00:54
Hello Duncan
The code works on the table of data when the data is stored on the top of the work sheet, however the spreadsheets that I need the code to work on the table is stored on row 7. I have tried to change the code to reflect the change in location but it keeps failing on the line of code;
The code works on the table of data when the data is stored on the top of the work sheet, however the spreadsheets that I need the code to work on the table is stored on row 7. I have tried to change the code to reflect the change in location but it keeps failing on the line of code;
Range("a1:f1").Value = Array("Die", "Machine", "Part Desc", "Part No", "Date", "Units") 1:
I think it just needs a couple of changes to work but I have had a play with it but unable to get it to work for me.
The spreadsheet I have attached is a example sheet of how the data is stored, and on sheet2 is example of how I want the data.
Regards
SQLSearcher
Expert: duncanb7 replied at 2024-07-23 02:44:27
Could it explain in detail for the question. What you want to
do on Row1 and Row7
Duncan
do on Row1 and Row7
Duncan
