Looping and adding data - Excel

[amal2004]

Hello ,

I am having so much trouble fixing an excel sheet to import it to ms access. I get a sheet from Ms Word with the data listed as below but with one problem is that the Name of broker is listed underneath the last row with his transactions . This is how the columns and row are listed.

Agent Acct Name Units Partner Date Amount
E777 478-487 5.33 321 5/3/2001 $46
E777 478-489 5.33 321 5/3/2001 $48
E777 478-482 5.33 311 5/3/2001 $49
Paul W.
Total

E778 488-882 6.66 368 5/3/2002 $55
E778 488-222 6.66 388 5/3/2002 $58
E778 488-112 6.66 344 5/3/2002 $60
E778 488-222 6.66 355 5/3/2002 $55
Michael Ewanou
Total

I need to find a way to loop through the Excel sheet and put the name of the broker beside every row he made a transaction … I really do appreciate any help..
Thanks

 

[ETID]

'This routine assumes that the header "Agent" is cell A1
Public r As Integer
Public Last_Record As Integer
Public Marker As Integer


Sub id_records()
Cells(1, 1).Select
Selection.EntireColumn.Insert
Cells(1, 1) = "Agent Name"
r = 2: Marker = r
Last_Record = InputBox("Enter the number of the Last row containing data.")
Do Until r > Last_Record
If Cells(r, 2) = "Total" Then
fill_em
Else
r = r + 1
End If
Loop

End Sub

Sub fill_em()
Do Until Marker = r - 1
Cells(Marker, 1) = Cells(r - 1, 2)
Marker = Marker + 1
Loop
Rows(CStr(r - 1) + ":" + CStr(r + 1)).Delete
r = r + 3
End Sub

 

[VBAjedi]

Since I'd already coded it before ETID posted, here's a bit of a different approach (working from the bottom to top):

Sub CleanUp()
Dim C As Range
Dim FirstRow, LastRow, CurrBroker
Dim x
FirstRow = InputBox("What is the first data row?")
LastRow = InputBox("What is the last Total row?")
For x = LastRow To FirstRow Step -1
   Set C = Range("B" & x)
   If C.Value = "Total" Then ' Broker name is in row above
      x = x - 1
      Set C = Range("B" & x)
      CurrBroker = C.Value
      C.Value = "" ' Didn't think you still wanted this here
   Else
      C.Offset(0, -1).Value = CurrBroker
   End If
Next x
End Sub

It doesn't automatically add the column, but that's easy to do if you want it.



VBAjedi

 

[amal2004]

ETID...

Thank you very much for this wonderful solution. The problem is that the string “Total” underneath the name in column “Agent” is not always there. I am sorry if I did mention that from the start. The name comes directly after the rows of data for that broker.
Is their any way we could modify the code to look at the length of the name if it is more that a sear ten number or something like that..

 

[amal2004]

VBAJEDI..

Thank you man, I really do appreciate your work. But I have to find something other than "total" to go through this process.

 

[VBAjedi]

No problem, but you must come up with a reliable way for the code to spot those names. We don't know the rules your data follows, so we can't do it for you. Some possibilities:

* If the cell does not contain any numbers, and the value is not "Total", it's a name
* If there are no contents in the Acct Name or Amount columns, that row contains a name

Once you figure out your data "rule", we can help you code it into one of these procedures.

VBAjedi

 

[amal2004]

Agent Acct Name Units Partner Date Amount
E777 478-487 5.33 321 5/3/2001 $46
E777 478-489 5.33 321 5/3/2001 $48
E777 478-482 5.33 311 5/3/2001 $49
Paul W. 999
Total
EMPTY ROW

E778 488-882 6.66 368 5/3/2002 $55
E778 488-222 6.66 388 5/3/2002 $58
E778 488-112 6.66 344 5/3/2002 $60
E778 488-222 6.66 355 5/3/2002 $55
Michael Melon 555
Total
EMPTY ROW
E778 488-882 6.66 368 5/3/2002 $55
E778 488-222 6.66 388 5/3/2002 $58
E778 488-112 6.66 344 5/3/2002 $60
E778 488-222 6.66 355 5/3/2002 $55
Michael Ewanou 777

EMPTY ROW
E777 478-487 5.33 321 5/3/2001 $46
E777 478-489 5.33 321 5/3/2001 $48
E777 478-482 5.33 311 5/3/2001 $55
Paul W. 999




Okay , I rules are
In column “A “there is the name and in column “C” there is a numeric value on the same line the name is ex("999')see above. So if we looked at the length of the name in column “A” and if IS Numeric column “C” then we need to take that name and count how many rows is above it and fill in the name in a new column beside every row and so on . Did this help? Thanks for your help in advance.

 

[VBAjedi]

Ok, give this a go:

Sub CleanUp()
Dim C As Range
Dim FirstRow, LastRow
Range("A1").EntireColumn.Insert
FirstRow = InputBox("What is the first data row?")
LastRow = InputBox("What is the last Total row?")
For x = LastRow To FirstRow Step -1
   Set C = Range("B" & x)
   If Len(C.Value) > 5 And Len(C.Offset(0, 2).Value) = 0 _
      And IsNumeric(C.Offset(0, 2).Value) Then '
      CurrBroker = C.Value
      C.Value = ""
   ElseIf Len(C.Value) > 0 And C.Value <> "Total" Then
      C.Offset(0, -1).Value = CurrBroker
   End If
Next x
End Sub

Let me know if that does it for you!

VBAjedi

 

[amal2004]

Thanks a million . i will test it and get back to you .....