Need a macro to evaluate and sift-through data

  • Thread starter Thread starter Ronny Hamida
  • Start date Start date
R

Ronny Hamida

I need to write a macro to do the following:

1) Remove any entire line when a value in column A
(labeled as JobCode) begins with a letter "R":

For instance -

Column A Column B
A539292 This line is a test
B192591 This line is another test
R182571 This entire line should be removed
C194712 This line is yet another test

2) Sort the data by Column C (already have this part done)

3) Sift through the data in Column C and inserting 2
blank lines after each time the first 4 digits in column C
changes:

For instance -

Column B Column C
Testing Line 2601999999
Testing Line 2601999999
Testing Line 2601999999
(blank line)
(blank line)
Testing Line 2602999999
Testing Line 2602999999
(blank line)
(blank line)
Testing Line 2603999999 (etc)

4) And if possible, while inserting the two blank lines,
is there something else I can do to automatically add what
is in Column D together between these blank lines and
change the font of this total? When I do this manually, I
use the AutoSum button and it works only because of the
blank lines seperating the rows.

Example:

Column C Column D
2601999999 1
2601999999 1
(blank) (total: 2 in red)
(blank line)
2602999999 1
2602999999 2
(blank) (total: 3 in red) etc.

As usual, thank you to everyone in advance who can help!
If it helps, my current macro labels the column headers
(which might be unnecessary). Also, my attempts to do
most of this have failed because whenever I wanted it to
evaluate 1 column, it continued to the next column. I've
since then removed that coding due to fustration... :-/

Confused as always,
Ronny
 
Hi
some ideas:
1. remove rows:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "A").Value,1) = "R" then
Cells(row_index, "A").EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

2. Sorting: you said you already did this :-)

3. Inserting rows / 4. Adding formulas:
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "C").Value,4) <> Left(Cells(row_index + 1,
"C").Value,4) _
Then
Cells(row_index + 1, "C").resize(2,1).EntireRow.Insert
(xlShiftDown)
Cells(row_index + 1, "D").formulaR1C1= _

"=SUMPRODUCT(--(LEFT(R1C3:R[-1]C3,4)=LEFT(R[-1]C3,4)),R1C4:R[-1]C4)"
Cells(row_index + 1, "D").font.colorindex=3
End If
Next
End Sub
 
Thank you, Frank, for the help, however there's just one
more dilema with the code for inserting blank lines and
totalling up Column D:

I think the code you gave goes from the bottom of the
spreadsheet, up. When it does this, the blank lines occur
just fine, but the totalling up (of each section) does
not. While watching the macro, it seems that the
totalling up works fine until more blank lines are put in
(seperating due to the data in Column C).

Example of what happens during the macro run (not a
completed run):
Column C Column D
2601999999 1
2601999999 1
2602999999 1
2602999999 2
(blank) (total: 5)
(blank line)
2604999999 5
2604999999 8
(blank) (total: #VALUE!)
(blank line)

^^^^^^^^^
A numerical value occurs at first until the blank lines
are inserted above. That's when I get the #VALUE! Also,
the numerical value that appears before the blank lines
are made above is incorrect (as seen where it
says, "total: 5") because the data before that has not
been processed for the Column C change yet. Does that
make any sense?

Is there any way to change it so that it goes down the
list? And if so, how can I accomodate a header (since the
header would be different than the data, thus, two blank
lines would occur under it)?

Thank you again!
Ronny
-----Original Message-----
Hi
some ideas:
1. remove rows:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "A").Value,1) = "R" then
Cells(row_index, "A").EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

2. Sorting: you said you already did this :-)

3. Inserting rows / 4. Adding formulas:
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "C").Value,4) <> Left(Cells (row_index + 1,
"C").Value,4) _
Then
Cells(row_index + 1, "C").resize (2,1).EntireRow.Insert
(xlShiftDown)
Cells(row_index + 1, "D").formulaR1C1= _

"=SUMPRODUCT(--(LEFT(R1C3:R[-1]C3,4)=LEFT(R[-1] C3,4)),R1C4:R[-1]C4)"
Cells(row_index + 1, "D").font.colorindex=3
End If
Next
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany

I need to write a macro to do the following:

1) Remove any entire line when a value in column A
(labeled as JobCode) begins with a letter "R":

For instance -

Column A Column B
A539292 This line is a test
B192591 This line is another test
R182571 This entire line should be removed
C194712 This line is yet another test

2) Sort the data by Column C (already have this part done)

3) Sift through the data in Column C and inserting 2
blank lines after each time the first 4 digits in column C
changes:

For instance -

Column B Column C
Testing Line 2601999999
Testing Line 2601999999
Testing Line 2601999999
(blank line)
(blank line)
Testing Line 2602999999
Testing Line 2602999999
(blank line)
(blank line)
Testing Line 2603999999 (etc)

4) And if possible, while inserting the two blank lines,
is there something else I can do to automatically add what
is in Column D together between these blank lines and
change the font of this total? When I do this manually, I
use the AutoSum button and it works only because of the
blank lines seperating the rows.

Example:

Column C Column D
2601999999 1
2601999999 1
(blank) (total: 2 in red)
(blank line)
2602999999 1
2602999999 2
(blank) (total: 3 in red) etc.

As usual, thank you to everyone in advance who can help!
If it helps, my current macro labels the column headers
(which might be unnecessary). Also, my attempts to do
most of this have failed because whenever I wanted it to
evaluate 1 column, it continued to the next column. I've
since then removed that coding due to fustration... :-/

Confused as always,
Ronny
Click to expand...

.
Click to expand...
 
Hi
could you post the formulas for both cells which are inserted by the
macro

--
Regards
Frank Kabel
Frankfurt, Germany

Ronny Hamida said:
Thank you, Frank, for the help, however there's just one
more dilema with the code for inserting blank lines and
totalling up Column D:

I think the code you gave goes from the bottom of the
spreadsheet, up. When it does this, the blank lines occur
just fine, but the totalling up (of each section) does
not. While watching the macro, it seems that the
totalling up works fine until more blank lines are put in
(seperating due to the data in Column C).

Example of what happens during the macro run (not a
completed run):
Column C Column D
2601999999 1
2601999999 1
2602999999 1
2602999999 2
(blank) (total: 5)
(blank line)
2604999999 5
2604999999 8
(blank) (total: #VALUE!)
(blank line)

^^^^^^^^^
A numerical value occurs at first until the blank lines
are inserted above. That's when I get the #VALUE! Also,
the numerical value that appears before the blank lines
are made above is incorrect (as seen where it
says, "total: 5") because the data before that has not
been processed for the Column C change yet. Does that
make any sense?

Is there any way to change it so that it goes down the
list? And if so, how can I accomodate a header (since the
header would be different than the data, thus, two blank
lines would occur under it)?

Thank you again!
Ronny
-----Original Message-----
Hi
some ideas:
1. remove rows:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "A").Value,1) = "R" then
Cells(row_index, "A").EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

2. Sorting: you said you already did this :-)

3. Inserting rows / 4. Adding formulas:
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Left(Cells(row_index, "C").Value,4) <> Left(Cells (row_index + 1,
"C").Value,4) _
Then
Cells(row_index + 1, "C").resize (2,1).EntireRow.Insert
(xlShiftDown)
Cells(row_index + 1, "D").formulaR1C1= _

"=SUMPRODUCT(--(LEFT(R1C3:R[-1]C3,4)=LEFT(R[-1] C3,4)),R1C4:R[-1]C4)"
Cells(row_index + 1, "D").font.colorindex=3
End If
Next
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany

I need to write a macro to do the following:

1) Remove any entire line when a value in column A
(labeled as JobCode) begins with a letter "R":

For instance -

Column A Column B
A539292 This line is a test
B192591 This line is another test
R182571 This entire line should be removed
C194712 This line is yet another test

2) Sort the data by Column C (already have this part done)

3) Sift through the data in Column C and inserting 2
blank lines after each time the first 4 digits in column C
changes:

For instance -

Column B Column C
Testing Line 2601999999
Testing Line 2601999999
Testing Line 2601999999
(blank line)
(blank line)
Testing Line 2602999999
Testing Line 2602999999
(blank line)
(blank line)
Testing Line 2603999999 (etc)

4) And if possible, while inserting the two blank lines,
is there something else I can do to automatically add what
is in Column D together between these blank lines and
change the font of this total? When I do this manually, I
use the AutoSum button and it works only because of the
blank lines seperating the rows.

Example:

Column C Column D
2601999999 1
2601999999 1
(blank) (total: 2 in red)
(blank line)
2602999999 1
2602999999 2
(blank) (total: 3 in red) etc.

As usual, thank you to everyone in advance who can help!
If it helps, my current macro labels the column headers
(which might be unnecessary). Also, my attempts to do
most of this have failed because whenever I wanted it to
evaluate 1 column, it continued to the next column. I've
since then removed that coding due to fustration... :-/

Confused as always,
Ronny
Click to expand...

.
Click to expand...
Click to expand...
 
Frank -

Here's a macro-made formula from the spreadsheet:

=SUMPRODUCT(--(LEFT($C$1:$C5114,4)=LEFT
($C5114,4)),$D$1:$D5114)

Same with this (earlier in the spreadsheet)

=SUMPRODUCT(--(LEFT($C$1:$C6,4)=LEFT($C6,4)),$D$1:$D6)

I have figured out what has happened. Everything works
(meaning I get the actual correct values) when I remove
the header from my spreadsheet as well as the two blank
lines that the macro puts under it. (It's putting the two
blank lines under the header (along with the formula to
add the column above, which only consists of the header
anyway) because the column has changed. But you knew that
already... ) :)
 
I have figured out exactly what is happening:

During the process where the macro inserts the blank lines
and adds the SUMPRODUCT formula, it does this for the
header. When it does the SUMPRODUCT for the header, all
of the other SUMPRODUCT gives off the #VALUE! error. I
can leave the header with no problem, but I need to get
rid of that last one, which is no problem. :)

But if there was a way to make that macro not check that
header line and to not add a SUMPRODUCT formula under it,
that would be cool, otherwise, I can just write in an
extra line to delete the line with the SUMPRODUCT for the
header.

THANK YOU VERY MUCH Frank!!!
 
Back
Top