Chemical Engineering Coop
New Member
- Joined
- Jun 1, 2011
- Messages
- 7
Hi Yall,
Would Like to create a report on a seperate worksheet (in the same excel document) in which presents all the data in 2 columns (time in 1 and comments in the other). My original worksheet contains 64 columns, 32 columns in which contain "work comments", and each of those columns has an companion column (to there left) with date and time for the "work comment". In some cases there is no work detail so both the "time" column and the "work detail" column are empty. The hardest part of the code is that the range of comments can change.
This is what I have written in VBA so far..
Description: As one can see here I am terrible at VB but I am trying. The VB first checks to see if there is a comment, if not it will move to the next column. When a "comment" is present the first thing I'd like it to do is copy the column header and place it in the "report" worksheet in column 2. After doing so I'd like to copy the comments and place it under the description header (now in column 2 of "report"). 1st problem is that I need the times (which are in the column to the left, next to each comment) to follow suite. These time would be in column 1 of the report.
So what I was thinking is that during the paste of either the comment or header, that there will be a (xlup) style function, so that it pastes in descending order with a Column header (comment location) followed by comments. The problem with doing this paste is that I need the time to follow the comments. I'd rather not concatenate if possible. So something like
commentRng.copy
'//then take the comment range rows and pull those rows from the column to the left ( the time column)
commentRng(2 * (i - 1) + 4).copy
'//using 4 instead of 5 as the column to the left is for time. Same range of rows but different column.
I also am not sure about pasting vs. destination or which one would be better for such varying ranges or whether or not you can paste using the (x1up) function.
I hope this might make sense to someone but if not I will re-post this question and include what the worksheet looks like (some reason the formatting in this posting box is making it difficult to use spaces and such to indicate cells when pasting in from excel)
Well, it's worth a shot. If anyone needs anything from me about this don't hesitate to ask, I will try my best to satisfy. Thanks ahead of time, I truly appreciate it.
-Sid
Would Like to create a report on a seperate worksheet (in the same excel document) in which presents all the data in 2 columns (time in 1 and comments in the other). My original worksheet contains 64 columns, 32 columns in which contain "work comments", and each of those columns has an companion column (to there left) with date and time for the "work comment". In some cases there is no work detail so both the "time" column and the "work detail" column are empty. The hardest part of the code is that the range of comments can change.
This is what I have written in VBA so far..
Sub CreateReport()
'this macro is to create an easily readable comment stacker report.
Dim commentRng As Range
Dim startrow As Long
Dim i As Integer
Dim numstations As Integer
numstations = 32
startrow = 19
For i = 1 To numstations
If Not Worksheets(Comments).Cells(startrow, 2 * (i - 1) + 5) = "" Then
Set commentRng = Worksheets(Comments).Range(Cells(startrow, 2 * (i - 1 ) + 5 ) , Cells(Row.Count, 2 * (i - 1) + 5).End(x1up).Row)
Worksheets(Comments).Cells(1, 2 * (i - 1) + 5).Copy
commentRng.Copy
End Sub
Description: As one can see here I am terrible at VB but I am trying. The VB first checks to see if there is a comment, if not it will move to the next column. When a "comment" is present the first thing I'd like it to do is copy the column header and place it in the "report" worksheet in column 2. After doing so I'd like to copy the comments and place it under the description header (now in column 2 of "report"). 1st problem is that I need the times (which are in the column to the left, next to each comment) to follow suite. These time would be in column 1 of the report.
So what I was thinking is that during the paste of either the comment or header, that there will be a (xlup) style function, so that it pastes in descending order with a Column header (comment location) followed by comments. The problem with doing this paste is that I need the time to follow the comments. I'd rather not concatenate if possible. So something like
commentRng.copy
'//then take the comment range rows and pull those rows from the column to the left ( the time column)
commentRng(2 * (i - 1) + 4).copy
'//using 4 instead of 5 as the column to the left is for time. Same range of rows but different column.
I also am not sure about pasting vs. destination or which one would be better for such varying ranges or whether or not you can paste using the (x1up) function.
I hope this might make sense to someone but if not I will re-post this question and include what the worksheet looks like (some reason the formatting in this posting box is making it difficult to use spaces and such to indicate cells when pasting in from excel)
Well, it's worth a shot. If anyone needs anything from me about this don't hesitate to ask, I will try my best to satisfy. Thanks ahead of time, I truly appreciate it.
-Sid