Results 1 to 13 of 13

Thread: [RESOLVED] Programmatically Apply The Same Formula To Each Cell In A User Selected Range

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Resolved [RESOLVED] Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    I am using VS 2008, and coding in VB.Net for an Excel 2007 Add-In.

    After the user selects a range of cells, they can open a form where they are asked to enter a custom formula into a text box. Using VB.net, how can I have that custom formula applied to every one of the users selected cells? For example, the user already has numbers in each of the selected cells. But, they need to increase the value in each cell by 25% programmatically.

    I have the form created. But, I am at a loss as to how to even start finding the users selected cell range. Not to mention how to apply their custom formula to the values in each of the selected cells.

    Any suggestion?

  2. #2

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    When I assign the cell range through code and the formula through code, it works just fine. As follows:

    Code:
    Dim rng As Range
    Dim cel As Range
    
    rng = ActiveSheet.Range("A1:A10").Cells
    
    For Each cel In rng
        cel.Value = cel.Value * 1.25
    Next cel
    How do I use code to find and use the value that is in the user selected cell range?
    How do I use the custom formula that the user enters into a text box?

  3. #3
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    Quote Originally Posted by fdegree View Post
    I am using VS 2008, and coding in VB.Net for an Excel 2007 Add-In.

    After the user selects a range of cells, they can open a form where they are asked to enter a custom formula into a text box. Using VB.net, how can I have that custom formula applied to every one of the users selected cells? For example, the user already has numbers in each of the selected cells. But, they need to increase the value in each cell by 25% programmatically.

    I have the form created. But, I am at a loss as to how to even start finding the users selected cell range. Not to mention how to apply their custom formula to the values in each of the selected cells.

    Any suggestion?
    the text in Blue doesn't make any sence
    1) a User has a Excel workbook open and a certain sheet.. say sheet1
    2) you want to keep track with a .NET App. what Range the User selects to apply a Formula

    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    Quote Originally Posted by ChrisE View Post
    the text in Blue doesn't make any sence
    1) a User has a Excel workbook open and a certain sheet.. say sheet1
    2) you want to keep track with a .NET App. what Range the User selects to apply a Formula

    Thanks for responding Chris...

    1) the User has an Excel workbook open and a certain worksheet....Sheet1
    2) the User selects any Range of Cells
    3) the User clicks a Button on the custom Ribbon
    4) a Form pops-up with a Text Box for the User to enter a custom formula
    5) after entering their custom formula, the User clicks the "Apply" button on that form and I want their custom formula to be applied to the value in each of the cells that they selected.

    I have the Button on the Ribbon and the Form that pops-up when that button is clicked. It's step 5 above that I'm struggling with.

    I have this code that is doing exactly what I want in step 5 above. Except the code is defining the Range ("A1:A10") and Formula (cel.Value = cel.Value * 1.25). Instead, I want the code to use the Users custom Range and custom formula....
    Code:
    Dim rng As Range
    Dim cel As Range
    
    rng = ActiveSheet.Range("A1:A10").Cells
    
    For Each cel In rng
        cel.Value = cel.Value * 1.25
    Next cel
    I hope this helps clarify things...Thanks!
    Last edited by fdegree; Oct 8th, 2020 at 06:54 AM.

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    I think you are in the wrong Forum
    should it not be the ..Office Development Forum...


    when working with .NET to Excel
    here a few samples
    note I'm in Germany so the Formula is SUMME don't know where you are but it might just be SUM
    Code:
    Option Strict On
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    
            'show UsedRanges
            Try
                Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
                Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
                xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
                Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle3"), Worksheet)
                Dim ColRange = xlSt.UsedRange.Columns.Count 'count the used Columns      
                Dim rowRange = xlSt.UsedRange.Rows.Count 'count the used rows
    
                MessageBox.Show(CStr(ColRange))
                MessageBox.Show(CStr(rowRange))
    
    
                xlApp.Quit()
                xlApp = Nothing
            Catch g As Exception
                MessageBox.Show(g.ToString)
            End Try
        End Sub
    
       
    
    
    
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            Try
                Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
                Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
                xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
                Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle3"), Worksheet)     
                Dim rowRange = xlSt.UsedRange.Rows.Count 'count the used rows
           
    
                'Dim rowRange = Worksheet.UsedRange.Rows.Count 'how many rows are used
                Dim mySum = rowRange + 1 'add Formula at the end so =  + 1
    
    
                With xlSt
    
                    '1.) sample
                    'Sum Column B
                    .Range("B" & mySum).FormulaLocal = "=SUMME(B2:B" & rowRange & ")"
    
                    '2.) sample
                    'Sum the rows and the result to Column G
                    'copy formula to range
                    '.Range("G2").FormulaLocal = "=SUMME(A2:F2)"
                    '.Range("G2").Copy()
                    '.Range("G3:G" & rowRange).PasteSpecial(XlPasteType.xlPasteFormulas)
    
                End With
                xlWb.Save()
                xlApp.Quit()
                xlApp = Nothing
            Catch g As Exception
                MessageBox.Show(g.ToString)
            End Try
        End Sub
    End Class
    good luck
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    It could technically be either .Net or Office but, since you're going to be working so closely with the Excel API, you're likely to get better answers in Office.

    Moved.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    Quote Originally Posted by ChrisE View Post

    good luck
    Thank you!

    I'll work with this and see what I come up with.

    One thing I have noticed.... UsedRange ....seems to return any cell that has data in it. But, what I'm looking for is the code that will "recognize" the range that the user selected.

    For example -- The user clicks on C3 and drags their mouse to F8. What code will "grab" that custom range, regardless of the cell format or contents?

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    am i missing something here? if you try to use a formula to increase the value in the same cell i will give a circular reference, and if saved will overwrite the previous value in the cell with the formula

    you can of course just use the code to increase the value in all the selected cells, but that is not really the same thing
    Code:
    Dim s  As Range
    Set s = xlapp.Selection
    For Each cel In s
        cel.Value = cel * 2
    Next
    you can convert this to your .net objects
    changing the value to be multiplied by is straightforward, but if the formula is more complex, you could do something like apply the formula to some other cell then copy the value to the original cell
    i tested this with a simple formula, a more complex formula should work just as well, though may need some formatting, you can get your formula from any input, i just cheated and got it from within the code
    Code:
    Dim s  As Range, cel As Range, c As Range, f As String
    textbox1 = "/2"
    Set c = Cells(Rows.Count, Columns.Count)
    Set s = Selection
    For Each cel In s
        c.Formula = "=" & cel.Address(False, False) & textbox1.Text
        cel.Value = c
    Next
    c.Clear
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    Quote Originally Posted by westconn1 View Post
    Code:
    Dim s  As Range
    Set s = Selection
    For Each cel In s
        cel.Value = cel * 2
    Next
    Thank you for helping....

    Your above code does exactly what I want, using VBA.
    However, when converting to VB.net, the " Selection " object is not recognized.
    The formula part, I think I got figured out. So, lets forget about applying the formula for now.

    How do I get the same result as " Set s = Selection " when using VB.net?
    I've tried " s = ActiveSheet.Range("C5:F12").Cells " Which does work.
    But, the code is assigning the range. I want the user to choose their own range and the code to recognize their selection.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    I've tried " s = ActiveSheet.Range("C5:F12").Cells " Which does work.
    even though this might work, which sort of surprises me, it is really bad programming and may lead to all sorts of error or other problems
    any object that is not fully qualified may prevent you application from closing cleanly and/or may leave additional instances of excel open in memory

    in the .net, you must have some object variable for the excel application, as i suggested in the snippet, the selection is a member of that object
    in .net the set keyword is not used
    and you may have to type cast the selection into the variable, if they are not intrinsically converted
    i would have no problem to make the code work from vb6, but i really struggled with .net when i tried it previously
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    Quote Originally Posted by westconn1 View Post
    even though this might work, which sort of surprises me
    Thank you for your input. I truly do appreciate it.
    In an attempt to keep things simple, I probably complicated it by only providing what I thought would the bare minimum needed. It appears I am mistaken. So, here is everything I have.

    This is an image of the Form the end-user is presented with when they click the new button that will go on the custom ribbon:
    Name:  ExcelForm.jpg
Views: 125
Size:  26.8 KB

    Here is the entire code associated with the "Apply" button on this form:
    Code:
    Private Sub Apply_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Apply.Click
    
            Dim FirstCell As String
            Dim LastCell As String
            Dim Multiplier As String
            Dim Warning1 As DialogResult
    
            'Check to see if there is a value entered into all text boxes
            FirstCell = TextBox1.Text
            LastCell = TextBox2.Text
            Multiplier = TextBox3.Text
            If FirstCell = "" Or LastCell = "" Or Multiplier = "" Then
                Warning1 = MsgBox("You neglected to enter a value in one of the boxes.  Please try again.", MsgBoxStyle.OkOnly + vbCritical, "WARNING...PLEASE READ!!!!!")
                If Warning1 = DialogResult.OK Then
                    Exit Sub
                End If
            End If
            Me.Close()
    
    
            'Multiply the factor by the values in the chosen cell range
            Dim rnge As Range
            Dim cel As Range
            Dim rnd1 As String
            Dim rnd5 As String
    
    
            rnge = Globals.ThisAddIn.Application.ActiveSheet.Range(FirstCell & ":" & LastCell).Cells
    
            If CheckBox1.Checked = True Then
    
                'Rounding to the nearest 5
                rnd5 = 5
                For Each cel In rnge
                    cel.Value = Math.Round(cel.Value * Multiplier / rnd5) * rnd5
                Next cel
    
            Else
    
                'Rounding to the nearest 1
                rnd1 = 1
                For Each cel In rnge
                    cel.Value = Math.Round(cel.Value * Multiplier / rnd1) * rnd1
                Next cel
    
            End If
    
    
        End Sub
    This is working just fine. However, not all of the end-users are knowledgeable enough to determine the first and last cell in their desired range. So, I don't want them to have to find and enter the first cell and last cell of their range on this form. Instead I want them to select the cell range with their mouse, or whatever other method they are comfortable with, prior to clicking the new button on the ribbon that launches this form. Then, I want to have the code get their custom range. So, in the end, I can remove those particular text boxes from the form and the only thing they will be entering on the form is their desired multiplier.

    I have tried and cannot seem to get your snippet to work. Perhaps I'm missing something. I am self-taught with VB.net and there is still a lot I do not know.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    at a guess, i would believe you should change to
    Code:
    rnge = Globals.ThisAddIn.Application.selection
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range

    Quote Originally Posted by westconn1 View Post
    at a guess, i would believe you should change to
    Code:
    rnge = Globals.ThisAddIn.Application.selection

    That's exactly what I am looking for. Such a simple solution that never occurred to me...as usual.

    THANK YOU!!! Sorry for not putting it all out there in the beginning. I'm sure I made this more complicated than it should have been.

    It never occurred to me that Selection is an object of the Application. I was trying everything except Application.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width