Create a list of unallocated payments
 

Help? When clicking a spreadsheet, I am trying to create a simple list
of customer numbers & payments from another spreadsheet whose payments
are unuallocated. For example:

On sheet "CRJournal":

No. Paymt Allocated
553 16.50 16.50
256 58.49
249 38.50 38.50
268 39.49
244 85.30
412 88.79 88.79
569 49.04 49.04
424 40.24


I need:

On Sheet "UallocatedCalcs":

No. Paymt
256 58.49
268 39.49
244 85.30
424 40.24

Any & all help with this sheet activate code will be greatly
appreciated.

mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=474481

Create a list of unallocated payments
 

This can be done without any programming:

1. copy the table to "UallocatedCalcs"
2. sort the table by Allocated
3. delete rows that are allocated
4. re-sort the table by No.

If you perform this frequently, use the Macro Recorder to capture the manual
operation.

Good Luck
--
Gary's Student


"mikeburg" wrote:


Help? When clicking a spreadsheet, I am trying to create a simple list
of customer numbers & payments from another spreadsheet whose payments
are unuallocated. For example:

On sheet "CRJournal":

No. Paymt Allocated
553 16.50 16.50
256 58.49
249 38.50 38.50
268 39.49
244 85.30
412 88.79 88.79
569 49.04 49.04
424 40.24


I need:

On Sheet "UallocatedCalcs":

No. Paymt
256 58.49
268 39.49
244 85.30
424 40.24

Any & all help with this sheet activate code will be greatly
appreciated.

mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=474481

Create a list of unallocated payments
 

Have you tried using autofilter, select blanks (on column C) and copy to
your other sheet?


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=474481

Create a list of unallocated payments
 

Here is some code:

Private Sub Worksheet_Activate()
Dim LRow As Long, Lrow2 As Long
Dim rng As Range, rng2 As Range, c As Range

LRow = Cells(Rows.Count, "A").End(xlUp).Row
If LRow = 1 Then LRow = 2
Set rng = Range("A2:B" & LRow)
rng.Cells.Clear
Lrow2 = Worksheets("CRJournal").Cells(Rows.Count, "A").End(xlUp).Row
Set rng2 = Worksheets("CRJournal").Range("A2:A" & Lrow2)
For Each c In rng2
If c.Offset(, 2).Value = "" Then
LRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("A" & LRow).Value = c.Value
Range("B" & LRow).Value = c.Offset(, 1).Value
Range("B" & LRow).NumberFormat = "0.00"
LRow = LRow + 1
End If
Next c
End Sub

Mike F
"mikeburg" wrote in
message ...

Help? When clicking a spreadsheet, I am trying to create a simple list
of customer numbers & payments from another spreadsheet whose payments
are unuallocated. For example:

On sheet "CRJournal":

No. Paymt Allocated
553 16.50 16.50
256 58.49
249 38.50 38.50
268 39.49
244 85.30
412 88.79 88.79
569 49.04 49.04
424 40.24


I need:

On Sheet "UallocatedCalcs":

No. Paymt
256 58.49
268 39.49
244 85.30
424 40.24

Any & all help with this sheet activate code will be greatly
appreciated.

mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=474481

Create a list of unallocated payments
 

The VBA code looks great. That's what I need because we work this shee
every day. However, I am so new to VBA I am having troubl
understanding it. I need to modify it to:

CRJournal's data starts on row A6

and

UAllocatedCalcs list needs to start on row A4

Can you help me make the modification & maybe comment the code to hel
me learn from it? Thanks a million. mikebur

--
mikebur
-----------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...fo&userid=2458
View this thread: http://www.excelforum.com/showthread.php?threadid=47448

Create a list of unallocated payments
 

Option Explicit

Private Sub Worksheet_Activate()
'by Mike Fogleman Oct 9, 2005
Dim LRow As Long, Lrow2 As Long
Dim rng As Range, rng2 As Range, c As Range

'The next 4 lines deal with sheet "UAllocatedCalcs"
'because Excel assumes code to be run on the active sheet
'unless told differently. This sheet must be active for
'the code to run by definition of the Worksheet_Activate event.

LRow = Cells(Rows.Count, "A").End(xlUp).Row 'finds last row
'with data in col A
If LRow = 3 Then LRow = 4 'if only Header exists start on next row
Set rng = Range("A4:B" & LRow) 'define existing data range
rng.Cells.Clear 'clear the range for new data

'Here we tell Excel to run code on a sheet that is not active
'by using a reference to the worksheet. Again we find the Last
'row in col A and define the range of data to analyze starting
'at row 6.

Lrow2 = Worksheets("CRJournal").Cells(Rows.Count, "A").End(xlUp).Row
Set rng2 = Worksheets("CRJournal").Range("A6:A" & Lrow2)

'We now loop down rng2 (Col A)1 cell at a time and evaluate
'the second cell to the right (Offset)if it is Blank ("")
'If True, then it runs the code following THEN
'If False, then it will skip to End If and loop to next cell.
For Each c In rng2
If c.Offset(0, 2).Value = "" Then
'each time we add data to "UAllocatedCalcs" we need to find
'the last row again and add 1 for the next row.
LRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
'this makes "A next row" = the current cell in the loop
Range("A" & LRow).Value = c.Value
'this makes "B next row" = 1 cell to right of current cell in loop
Range("B" & LRow).Value = c.Offset(0, 1).Value
'formats for 2 decimal places
Range("B" & LRow).NumberFormat = "0.00"
End If
Next c 'loop back to line: For Each c In rng2
End Sub


This should fix the start positions and explain how it does it.
Watch for word wrapping when you paste the code, it should show up red.
Mike F
"mikeburg" wrote in
message ...

The VBA code looks great. That's what I need because we work this sheet
every day. However, I am so new to VBA I am having trouble
understanding it. I need to modify it to:

CRJournal's data starts on row A6

and

UAllocatedCalcs list needs to start on row A4

Can you help me make the modification & maybe comment the code to help
me learn from it? Thanks a million. mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=474481