In case Sandy's explanation was not good enough. Here is a breakdown of the
code with the explanation underneath each command line.
I also note the there is no alternative action in the code in case H1 is
not greater than zero.
Sub Signonoff()
'Procedure title
Application.ScreenUpdating = False
'Turns off the automatic updating process for new entries
Sheets("Actual Sign-on Sign-Off").Range("A2:R359").ClearContents
'Clears the data from cells in A2 through R359 but leaves formulas and
formats intact.
k = 1
'assigns a value of 1 to the variable letter k
For l = 1 To 1500
'sets the parameter value of 1 through 1500 for variable letter l
If Sheets("Raw Data").Cells(l, 8) > 0 Then
k = k + 1
'If cell H1 on a sheet named Raw Data is greater than zero then add 1 to the
value of k
Sheets("Actual Sign-on Sign-Off").Cells(k, 1) = Sheets("Raw
Data").Cells(l, 3)
'On a sheet named Actual Sign-on Sign-off first iteration, Cell A2 is set to
the value of cell C1 on the sheet named Raw Data. These cell references will
change with each iteration. For example, the next time will be if H2 is
greater than zero then A3 = C2
For i = 2 To 20
'Sets the parameter value of 2 through 20 for variable letter i
j = 2 * i
'Sets the variable letter j to equal 2 times the variable value of i, which
on the first iteration would be a value of 4, second iteration = 6, and
increment by 2 for up to a maximum of 40
If Sheets("Raw Data").Cells(l + i, 12) = "" Then
Exit For
'If on the first iteration, Cell L3 is blank then exit this loop, which
would then begin the next iteration which checks the values in column H
Else
'If the conditional cell in column L is not blank then do the following
Sheets("Actual Sign-on Sign-Off").Cells(k, -1 + j) = Sheets("Raw
Data").Cells(l + i, 12)
Sheets("Actual Sign-on Sign-Off").Cells(k, -2 + j) = Sheets("Raw
Data").Cells(l + i, 10)
'On the first iteration, set Cell C2 of the sheet named sign-on sign-off
equal the value of cell J10 on a sheet named Raw Data and
On the sheet named sign-on sign-off set cell B2 = J3 of Raw Data
End If
'End the second conditional test and events
Next i
'Sends the program back to the For i statement and all the variables except
l automatically increment by 1 until all of the i conditions have been tested.
End If
'Ends the first conditional test and events
Next l
'Sends the program back to the For l statement and increments the l variable
by 1 and the other variables will reset as they are processed on this and
subsequent iterations until all 1500 iterations have run
Application.ScreenUpdating = True
'Turns Screen updating back on
End Sub
'Finished
"Sandy" wrote:
> In a nutshell this code loops, so it would take a lot of typing to
> explain the whole thing. I'll go through the first loop to start you
> off...
>
> The code first suspends the screenupdating it then clears the contents
> of Range A2 to R359 in sheet "Actual Sign-on Sign-Off". Next it checks
> whether cell H1 in sheet "raw data" is >0, if it is, then cell A2 in
> sheet "Actual sign-on sign-off" = Cell C1 in sheet "Raw Data". If the
> above was true (meaning H1 was >0) it then checks whether cell L3 is
> not blank, if it isn't, then cell C2 in sheet "Actual......" = cell L3
> in sheet "Raw Data" and cell B2 in sheet "Actual....." = cell J3 in
> sheet "Raw Data"
>
> It will now loop incrementing the values of "l, k, j, and i" some of
> them depending on if things are true or false so it would be hard to
> keep going. In the end it re-enables the screen updating and then ends.
>
> Some questions for you in what I explained above is the first set of
> data being properly placed? If not try and give a description of where
> they should be(using my previous paragraphs) and I might be able to
> help try to place the value correctly.
>
> Sandy
>
>
>
> (E-Mail Removed) wrote:
> > I have a spreadsheet that contains VBA code, that basically re-arranges
> >
> > an imported file. There are two tabs in the file, Raw Data and Actual
> > Sign on Sign Off. The problem I have is that the code was not written
> > by me and since then the imported data layout has changed.
> >
> > Main problem is that unless I highlight the blank cells and hit the
> > Delete key the macro does not position the data correctly, but once I
> > use the delete key it all works fine, I dont understand why this is.
> >
> >
> > Here is the code in the VBA - if someone could first of all tell my
> > what the code is trying to do (in simple terms) and how can I get
> > around the problem of the data not positioning in the tab Actual Sign
> > on Sign Off.
> >
> >
> > Sub Signonoff()
> > '
> > ' Sign On Off
> > ' Macro recorded 09/10/2002 by pcond1
> > '
> >
> >
> > '
> > Application.ScreenUpdating = False
> > Sheets("Actual Sign-on Sign-Off").Range("A2:R359").ClearContents
> > k = 1
> > For l = 1 To 1500
> > If Sheets("Raw Data").Cells(l, 8) > 0 Then
> > k = k + 1
> > Sheets("Actual Sign-on Sign-Off").Cells(k, 1) = Sheets("Raw
> > Data").Cells(l, 3)
> > For i = 2 To 20
> > j = 2 * i
> > If Sheets("Raw Data").Cells(l + i, 12) = "" Then
> > Exit For
> > Else
> > Sheets("Actual Sign-on Sign-Off").Cells(k, -1 + j) = Sheets("Raw
> > Data").Cells(l + i, 12)
> > Sheets("Actual Sign-on Sign-Off").Cells(k, -2 + j) = Sheets("Raw
> > Data").Cells(l + i, 10)
> > End If
> > Next i
> > End If
> > Next l
> > Application.ScreenUpdating = True
> > End Sub
>
>
Similar Threads
