Few Details required of Individual's Name with Range Address

S

SamDsouza

Board Regular
Joined
Apr 16, 2016
Messages
205
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

[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Individual Name:[/TD]
[TD]Jerry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]02-04-2015[/TD]
[TD]vchr no : 2[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]03-04-2015[/TD]
[TD]vchr no : 3[/TD]
[TD]200[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]04-04-2015[/TD]
[TD]Agst vchr no : 2[/TD]
[TD][/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]800[/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Balance :[/TD]
[TD][/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Individual Name:[/TD]
[TD]Mike[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]02-04-2015[/TD]
[TD]vchr no : 4[/TD]
[TD]1750[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]03-04-2015[/TD]
[TD]Agst vchr no : 4[/TD]
[TD][/TD]
[TD]350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]1750[/TD]
[TD]350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]Balance :[/TD]
[TD][/TD]
[TD]1400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Individual Name:[/TD]
[TD]Kate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]01-04-2015[/TD]
[TD]vchr no : 5[/TD]
[TD]1500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]05-04-2015[/TD]
[TD]vchr no : 6[/TD]
[TD]600[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]06-04-2015[/TD]
[TD]Agst vchr no : 12[/TD]
[TD][/TD]
[TD]2000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]09-04-2015[/TD]
[TD]Agst vchr no : 5[/TD]
[TD][/TD]
[TD]1500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]10-04-2015[/TD]
[TD]Agst vchr no : 2[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]2100[/TD]
[TD]3800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD]Balance :[/TD]
[TD]1700[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Individual Name:[/TD]
[TD]Steve[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Transaction Date[/TD]
[TD]Description[/TD]
[TD]Payments Made[/TD]
[TD]Payments Received[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]02-04-2015[/TD]
[TD]vchr no : 4[/TD]
[TD]1235[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]03-04-2015[/TD]
[TD]Agst vchr no : 4[/TD]
[TD][/TD]
[TD]1235[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]1235[/TD]
[TD]1235[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Your help will be appreciated
Thanks
SamD
 
Last edited:

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.
Sort by date Sort by votes
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
Click to expand...
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
NEVER used merged cells, they are about the worst thing you can do with a worksheet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Part 2 : Few Details required of Individual's Name with Range Address in ComboBox
Replies
8
Views
841
Fluff
Fluff
S
  • Question Question
Searching Value in ComboBox as per comboBox Columns
Replies
0
Views
591
SamDsouza
S
A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
340
Alaa mg
A
R
  • Question Question
Help!
Replies
3
Views
2K
63falcondude
63falcondude

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top