Help in modifying a macro

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,638
Office Version
2016
Platform
Windows
Hello ,
I have this challenge here:
I am using this code to add on my userform then I got into a big trap and I need help to get out.
Now the textboxes I am adding are all not having the same naming as the “Rw”. One is Rw10 and the other is Reg10. I wish I can change them all to have the same naming but that will give me much headache so I am thinking of a way to add them from the macro I have below. Thanks in advance.
Kelly
Code:
Sub oSum()
    Dim n As Integer, s As Double, TbRay As Variant
    TbRay = Array(10, 30)
    For n = 0 To UBound(TbRay)
        s = s + Val(Userform1.Controls("Rw" & TbRay(n)).Object.Value)
        
    Next n
     Userform1.Label1.Caption = s
End Sub
 

ask2tsp

Active Member
Joined
Feb 18, 2015
Messages
484
Kelly can you give more information about the form?
then I got into a big trap
What is the problem here?
I wish I can change them all to have the same naming
Explain what you have to do. What would you want the naming to look like?
so I am thinking of a way to add them from the macro I have below.
This code works fine if you only want to sum Rw.. values.
It is unclear what your question is. How many fields are there on the form? What gives you the headache?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,638
Office Version
2016
Platform
Windows
Kelly can you give more information about the form?

What is the problem here?

Explain what you have to do. What would you want the naming to look like?

This code works fine if you only want to sum Rw.. values.
It is unclear what your question is. How many fields are there on the form? What gives you the headache?
Sure the code works fine when adding the Rw values yet I wanna see if there is a way I can add an Rw value and a Reg value. Say I have some sets of the textbox named Reg and others named Rw. Now the wish is how to add one Reg and one Rw.

I hope this is clear now.

Thanks
 

ask2tsp

Active Member
Joined
Feb 18, 2015
Messages
484
See if this code works for you
Code:
Sub oSum()
   Dim n As Integer, s As Double, TbRay As Variant, _
       ufc As Variant
       
   TbRay = Array(10, 30)
   For Each ufc In UserForm1.Controls
      For n = 0 To UBound(TbRay)
         If ufc.Name = "Reg" & TbRay(n) _
         Or ufc.Name = "Rw" & TbRay(n) Then
            s = s + Val(ufc.Object.Value)
         End If
      Next n
   Next ufc
   UserForm1.Label1.Caption = s
End Sub
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,638
Office Version
2016
Platform
Windows
Hi, the code is doing the addition but the result is doubled.

For example when I enter 3 and 6 in the textboxes, it showed 18 instead of 9
 

ask2tsp

Active Member
Joined
Feb 18, 2015
Messages
484
I assumed that for any number n there is a Rw<n> or a Reg<n> but not both. If they both exist then which one should I select?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,638
Office Version
2016
Platform
Windows
I am selecting both. I am adding the data from both Reg and Rw.
They will both exist always.

Thanks
 

ask2tsp

Active Member
Joined
Feb 18, 2015
Messages
484
Now I'm confused because that's exactly what the sub does: add the values off all the controls whose name is "Rw" or "Reg" followed by one of the numbers in tbarray. So in your case s=Rw10+Rw30+Reg10+Reg30. If you say it computes the double then I really would like to see what your form looks like. Drop your workbook on some file sharing platform and publish the link in a reply.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,638
Office Version
2016
Platform
Windows
From your equation above,
s should be ;
s = Reg10 + Rw10 only.

When that is done problem will be solved.
Okay I think i have just resolved the issue :

Set TbRay = Array (10) and it worked. Wow that's a great Sub you came up with.

Thanks a lot
Kelly
 

ask2tsp

Active Member
Joined
Feb 18, 2015
Messages
484
If that works then this is an easier way
Code:
Sub oSumSimpler()
   Dim s As Double, ufc As Variant
       
   For Each ufc In UserForm1.Controls
      If ufc.Name = "Reg10" Or ufc.Name = "Rw10" Then
         s = s + Val(ufc.Object.Value)
      End If
   Next ufc
   UserForm1.Label1.Caption = s
End Sub
or even more compact, if you're sure those two controls exist
Code:
Sub oSumEvenSimpler()
   Dim s As Double
       
   s = Val(UserForm1.Rw10.Object.Value) _
     + Val(UserForm1.Reg10.Object.Value)
   UserForm1.Label1.Caption = s
End Sub
 
Last edited:

Forum statistics

Threads
1,082,367
Messages
5,365,028
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top