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

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



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

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



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

Another response in excel.misc

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
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

One other note: If you are manipulating the data in the file by deleting
rows or columns then the accuracy of the calculations resulting from the
macro are questionable.

"JLGWhiz" wrote:

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



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