Please provide VBA code

charnaliaamit

New Member
Joined
Jun 15, 2012
Messages
6
Hi

I request you to please provide me a code that provide the following solution as per the below query

I am using Excel 2007, I want the code for the following sheet profile, have two sheet in one work sheet, for eg. first sheet contain this data as shown below
S.No.Bill No.Doctor NameChemist NameMedicine NameQuantityUnitRateAmountDiscount RateDiscountTax RateTaxNet Amount
11021aCok-101Pcs10105%154554
21032bsunwin1Pc18205%1595114
31043aCok-102cap10405%25190228
41054dcoziwin3Pc20605%35285342
51065asunwin4Pcs10505%35235282
61076bCok-107cap251755%95830996
71087acoziwin8Pc10705%45330396
81098bsunwin9Pcs30905%55425510
91109dcoziwin12Pc291005%55475570
1011110asunwin34cap103405%17516151938
1111211bCok-1025Pc163005%15514251710
1211312ccoziwin15Pcs182005%1059501140
1311413asunwin16cap193045%15514451734
1411514cCok-1018Pcs10235%15110132
1511615acoziwin20Pc132605%13512351482

<TBODY>
</TBODY>

and iind sheet contain
Doctor Name,Net Amount

<TBODY>
</TBODY>


Now i want that code which provide me total of net amount by doctor name in iind sheet, I previously post this Query but no one provide me the code, i Know this will solve by pivot table, but it is very happier to me if i got VBA Code, I need this code due to some reasons


Thanks in Advance
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

b.downey

Active Member
Joined
Oct 16, 2011
Messages
484
You are right, the problem can be easily resolved via a Pivot table, but you asked for code and here it is.

The Code assumes that the Data as you describe is in "Sheet1" and also assumes that there is a second sheet called "Result" that will contain the results

Here is the code:

Code:
Option Explicit
Type TypeRec
    Name As String
    Net As Long
End Type
Sub ProcessYTD()
    Const ResultSheetName As String = "Result"
    Dim Wb As Workbook
    Dim Ws As Worksheet
    
    Dim WsResult As Worksheet
    
    Dim RowNo As Long
    Dim Rec() As TypeRec
    Dim IDX As Long
    Dim strName As String
    
    ReDim Rec(0)
    
    Set Wb = ThisWorkbook
    Set WsResult = Wb.Sheets(ResultSheetName)
    Set Ws = Wb.Sheets("Sheet1")
    For RowNo = 2 To Ws.Cells(Ws.Rows.Count, "A").End(xlUp).Row - 1
        IDX = FindIdx(Rec, Trim(Ws.Cells(RowNo, "C")))
        Rec(IDX).Net = Rec(IDX).Net + Val(Ws.Cells(RowNo, "N"))
    Next RowNo
    
    WsResult.Cells.ClearContents
    WsResult.Cells(1, "A") = "Name"
    WsResult.Cells(1, "B") = "Net"
    
    For IDX = 1 To UBound(Rec)
        WsResult.Cells(IDX + 1, "A") = Rec(IDX).Name
        WsResult.Cells(IDX + 1, "B") = Rec(IDX).Net
    Next IDX
    WsResult.Activate
    
    MsgBox "Complete", vbInformation
End Sub

Function FindIdx(Rec() As TypeRec, ByVal strName As String) As Long
    Dim IDX As Long
    For IDX = 1 To UBound(Rec)
        If strName = Rec(IDX).Name Then
            FindIdx = IDX
            Exit Function
        End If
    Next IDX
    
    ReDim Preserve Rec(IDX)
    Rec(IDX).Name = strName
    FindIdx = IDX
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top