Few Details required of Individual's Name with Range Address

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
196
Hi I would like to get the range Address of Each Individual Name against payment made to them and received from them and few other details from each individual name.
All the names are in column B if you see below

For eg if I type Kate in txtName.Text to get the range Address ie A15:D23 in txtRngAdd.Text
And txtRngFrom.Text = A15 and txtRngTo.Text = D23

2nd thing to get Kate’s Total Payment made in txtPymnt.Text = 2100 and Total Payment Received
txtPymtRcd.Text = 3800 and txtBalance.Text = 1700
and in Steve’s Case as the Balance is equal so there is no row of balance
The following is the structure of worksheet

ABCD
1Individual Name:Jerry
2Transaction DateDescriptionPayments MadePayments Received
302-04-2015vchr no : 2600
403-04-2015vchr no : 3200
504-04-2015Agst vchr no : 2600
6Total800600
7Balance :200
8
9Individual Name:Mike
10Transaction DateDescriptionPayments MadePayments Received
1102-04-2015vchr no : 41750
1203-04-2015Agst vchr no : 4350
13Total1750350
14Balance :1400
15Individual Name:Kate
16Transaction DateDescriptionPayments MadePayments Received
1701-04-2015vchr no : 51500
1805-04-2015vchr no : 6600
1906-04-2015Agst vchr no : 122000
2009-04-2015Agst vchr no : 51500
2110-04-2015Agst vchr no : 2300
22Total21003800
23Balance :1700
24
25Individual Name:Steve
26Transaction DateDescriptionPayments MadePayments Received
2702-04-2015vchr no : 41235
2803-04-2015Agst vchr no : 41235
29Total12351235

<tbody>
</tbody>


Your help will be appreciated
Thanks
SamD
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,275
I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
196
Mumps
Thanks for your query.
Infact only a userform with the above mentioned textboxes post#1
No confidential information at all.
Explanation : as per post #1
my main concern is to get range address and from that range get Total Payment, Payment Recieved and Balance of particular individual
also when getting range address want seperate value of Range From and Range To in respective text boxes
as suggested uploaded with below link

https://www.dropbox.com/s/xe9tqogkq4ctipq/GetRangeAddress-StructureXLMR.xlsm?dl=0

Thankx
SamD
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,420
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Private Sub txtName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Dim Fnd As Range
   Dim Rw As Long
   
   Set Fnd = Sheet1.Range("B:B").Find(Me.txtName, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      With Fnd.CurrentRegion
         If .Cells(.Rows.Count, 2) = "Balance :" Then Rw = .Rows.Count - 1 Else Rw = .Rows.Count
         Me.txtPymnt = .Cells(Rw, 3)
         Me.txtPymtRcd = .Cells(Rw, 4)
         Me.txtBalance = .Cells(Rw + 1, 3)
         Me.txtRngAdd = .Address(0, 0)
         Me.txtRngFrom = Split(.Address(0, 0), ":")(0)
         Me.txtRngTo = Split(.Address(0, 0), ":")(1)
      End With
   End If
End Sub
How do you want to display the balance as there are 2 possible cells, but only one txtbox
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,275

ADVERTISEMENT

Click here to download your file. Just click the "Enter Name" button. After entering the name in the txtName box, press the RETURN key.

This is the code:
Code:
Private Sub txtName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Application.ScreenUpdating = False
    Dim fnd As Range, total As Range, srcWS As Worksheet, LastRow As Long
    Set srcWS = Sheets("Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    txtRngAdd = ""
    txtRngFrom = ""
    txtRngTo = ""
    txtPymnt = ""
    txtPymtRcd = ""
    txtBalance = ""
    Set fnd = srcWS.Range("B1:B" & LastRow).Find(txtName, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Set total = srcWS.Range("B" & fnd.Row + 1 & ":B" & LastRow).Find("Total", LookIn:=xlValues, lookat:=xlWhole)
        If total.Offset(1, 0) = "Balance :" Then
            txtRngAdd = "A" & fnd.Row & ":" & "D" & total.Row + 1
            txtRngFrom = "A" & fnd.Row
            txtRngTo = "D" & total.Row + 1
            txtPymnt = total.Offset(0, 1).Value
            txtPymtRcd = total.Offset(0, 2).Value
            txtBalance = total.Offset(1, 2).Value
        Else
            txtRngAdd = "A" & fnd.Row & ":" & "D" & total.Row
            txtRngFrom = "A" & fnd.Row
            txtRngTo = "D" & total.Row
            txtPymnt = total.Offset(0, 1).Value
            txtPymtRcd = total.Offset(0, 2).Value
        End If
    Else
        MsgBox ("Name entered not found.")
    End If
    Application.ScreenUpdating = True
End Sub
 

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
196
Thanks Fluff and Mumps for your valuable inputs. Really Amazing :)
Fluff
How do you want to display the balance as there are 2 possible cells, but only one txtbox
Yes, lblBalance.Caption = "Balance"
if the Balance figure shows in coloumn of Payments Recieved then lblBalance.Caption = "Balance : To be Recieved"
if the Balance figure shows in coloumn of Payments Made then lblBalance.Caption = "Balance : To be Paid"

in other words
if the (sum of total or Total) of Payments Recieved is Greater than (sum of total or Total) of Payments Made then
lblBalance.Caption = "Balance : To be Paid"
if the (sum of total or Total) of Payments Made is Greater than (sum of total or Total) of Payments Recieved
lblBalance.Caption = "Balance : To be Recieved"

Mumps
Your balance figure shows perfectly for Payments Received excepts for Kate's case as the balance is in column of Payment made


SamD
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,420
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about
Code:
Private Sub txtName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Dim Fnd As Range
   Dim Rw As Long
   
   Set Fnd = Sheet1.Range("B:B").Find(Me.txtName, , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then
      With Fnd.CurrentRegion
         If .Cells(.Rows.Count, 2) = "Balance :" Then Rw = .Rows.Count - 1 Else Rw = .Rows.Count
         Me.txtPymnt = .Cells(Rw, 3)
         Me.txtPymtRcd = .Cells(Rw, 4)
         Me.txtRngAdd = .Address(0, 0)
         Me.txtRngFrom = Split(.Address(0, 0), ":")(0)
         Me.txtRngTo = Split(.Address(0, 0), ":")(1)
         If .Cells(Rw + 1, 3) <> "" Then
            Me.txtBalance = .Cells(Rw + 1, 3)
            Me.lblBal.Caption = "Balance : To be Paid"
         ElseIf .Cells(Rw + 1, 4) <> "" Then
            Me.txtBalance = .Cells(Rw + 1, 4)
            Me.lblBal.Caption = "Balance : To be Recieved"
         Else
            Me.txtBalance = ""
            Me.lblBal.Caption = "Balance"
         End If
      End With
   End If
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,275
Try:
Code:
Private Sub txtName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Application.ScreenUpdating = False
    Dim fnd As Range, total As Range, srcWS As Worksheet, LastRow As Long
    Set srcWS = Sheets("Sheet1")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    txtBalance = ""
    Set fnd = srcWS.Range("B1:B" & LastRow).Find(txtName, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Set total = srcWS.Range("B" & fnd.Row + 1 & ":B" & LastRow).Find("Total", LookIn:=xlValues, lookat:=xlWhole)
        If total.Offset(1, 0) = "Balance :" Then
            txtRngAdd = "A" & fnd.Row & ":" & "D" & total.Row + 1
            txtRngFrom = "A" & fnd.Row
            txtRngTo = "D" & total.Row + 1
            txtPymnt = total.Offset(0, 1).Value
            txtPymtRcd = total.Offset(0, 2).Value
            If txtName = "Kate" Then
                txtBalance = total.Offset(1, 1).Value
            Else
                txtBalance = total.Offset(1, 2).Value
            End If
        Else
            txtRngAdd = "A" & fnd.Row & ":" & "D" & total.Row
            txtRngFrom = "A" & fnd.Row
            txtRngTo = "D" & total.Row
            txtPymnt = total.Offset(0, 1).Value
            txtPymtRcd = total.Offset(0, 2).Value
        End If
    Else
        MsgBox ("Name entered not found.")
    End If
    Application.ScreenUpdating = True
End Sub
 
Last edited:

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
196
Fluff Thanks a tonne and Mumps you too for your solutions.
just a last question if the word "Balance :" is merged with cells of column A and column B of respective row and centrally aligned

will it better to keep the structure as is without merging or if i merge will it have any effect on finding the value of txtname or on the range
if i change the structure with merging i know i have to change the following
Code:
'Mumps
Set fnd = srcWS.Range("B1:B" & LastRow).Find(txtName, LookIn:=xlValues, lookat:=xlWhole)

'Fluffs
Set fnd = Sheet1.Range("B:B").Find(Me.txtName, , , xlWhole, , , False, , False)

will change to
'Mumps
Set fnd = srcWS.Range("[COLOR=#ff0000][B]A1:A[/B][/COLOR]" & LastRow).Find(txtName, LookIn:=xlValues, lookat:=xlWhole) 

'Fluff's
Set fnd = Sheet1.Range("[B][COLOR=#ff0000]A:A[/COLOR][/B]").Find(Me.txtName, , , xlWhole, , , False, , False)
Thanks guys for your valuable solution
SamD
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,420
Office Version
  1. 365
Platform
  1. Windows
NEVER used merged cells, they are about the worst thing you can do with a worksheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,312
Messages
5,635,497
Members
416,860
Latest member
coen078

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