VB code for a complicated report.

[ajaf123]

hello all. Im working on a report in the following format.

ID Name Type Amount

1001 AAAA RF 100
-- -- TF (in cell below RF) 200

1002 BBB TF 150

11 CCC RF 90

1075 DDD FAP 150
-- -- RF(in cell below FAP) 250
-- -- TF(in cell below RF) 1000

I want a macro that would bring all the data with respective ID in one line in a standard format. For the above data the report woul look like the following:

ID Name Type Amount
1001 AAAA RF 100 TF 200 - -

1002 BBB - - TF 150 - -

11 CCC RF 90 - - - -

1075 DDD RF 250 TF 1000 FAP 150

Notice how RF is always in first type column otherwise its empty. Similarly TF is always in the second type column if its there with a particular ID. Types include RF,TF and FAP. If an ID has more than 1 type, then ID and Name only appear once. Kindly help.

 

[veyselemre]

Hi,
I know very little English.I hope I have understood correctly.

Sub test()
    rw = 2
    For i = 2 To Range("a" & Rows.Count).End(xlUp).Row
        amntRF = "": amntTF = "": amntFAP = "": skp = 0
        If Cells(i, 1) <> "" Then
            If Cells(i, 3) = "RF" Then amntRF = Cells(i, 4)
            If Cells(i, 3) = "TF" Then amntTF = Cells(i, 4)
            If Cells(i, 3) = "FAP" Then amntFAP = Cells(i, 4)
            If Cells(i + 1, 1) = "" Then
                If Cells(i + 1, 3) = "RF" Then amntRF = Cells(i + 1, 4)
                If Cells(i + 1, 3) = "TF" Then amntTF = Cells(i + 1, 4)
                If Cells(i + 1, 3) = "FAP" Then amntFAP = Cells(i + 1, 4)
                skp = 1

                If Cells(i + 2, 1) = "" Then
                    If Cells(i + 2, 3) = "RF" Then amntRF = Cells(i + 2, 4)
                    If Cells(i + 2, 3) = "TF" Then amntTF = Cells(i + 2, 4)
                    If Cells(i + 2, 3) = "FAP" Then amntFAP = Cells(i + 2, 4)
                    skp = skp + 1
                End If
            End If
        End If

        Cells(rw, "H") = Cells(i, 1)
        Cells(rw, "I") = Cells(i, 2)
        If amntRF <> "" Then Cells(rw, "J") = "RF": Cells(rw, "K") = amntRF
        If amntTF <> "" Then Cells(rw, "L") = "TF": Cells(rw, "M") = amntTF
        If amntFAP <> "" Then Cells(rw, "N") = "FAP": Cells(rw, "O") = amntFAP
        rw = rw + 1
        i = i + skp
    Next
End Sub

 

[veyselemre]

Sub test2()
    rw = 1
    For i = 2 To Range("c" & Rows.Count).End(xlUp).Row
        If Cells(i, 1) <> "" Then
            rw = rw + 1
            Cells(rw, "H") = Cells(i, 1)
            Cells(rw, "I") = Cells(i, 2)
        End If
        If Cells(i, 3) = "RF" Then Cells(rw, "J") = "RF": Cells(rw, "K") = Cells(i, 4)
        If Cells(i, 3) = "TF" Then Cells(rw, "L") = "TF": Cells(rw, "M") = Cells(i, 4)
        If Cells(i, 3) = "FAP" Then Cells(rw, "N") = "FAP": Cells(rw, "O") = Cells(i, 4)
    Next
End Sub