-
Oct 7th, 2020, 08:00 PM
#1
Thread Starter
Lively Member
[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?
-
Oct 7th, 2020, 11:14 PM
#2
Thread Starter
Lively Member
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?
-
Oct 8th, 2020, 12:41 AM
#3
Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range
Originally Posted by fdegree
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.
-
Oct 8th, 2020, 06:50 AM
#4
Thread Starter
Lively Member
Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range
Originally Posted by ChrisE
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.
-
Oct 8th, 2020, 09:02 AM
#5
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.
-
Oct 9th, 2020, 06:48 AM
#6
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
-
Oct 9th, 2020, 09:09 AM
#7
Thread Starter
Lively Member
Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range
Originally Posted by ChrisE
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?
-
Oct 10th, 2020, 03:39 AM
#8
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
-
Oct 10th, 2020, 10:20 AM
#9
Thread Starter
Lively Member
Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range
Originally Posted by westconn1
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.
-
Oct 10th, 2020, 09:22 PM
#10
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
-
Oct 11th, 2020, 04:08 PM
#11
Thread Starter
Lively Member
Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range
Originally Posted by westconn1
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:
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.
-
Oct 12th, 2020, 03:08 AM
#12
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
-
Oct 12th, 2020, 09:12 AM
#13
Thread Starter
Lively Member
Re: Programmatically Apply The Same Formula To Each Cell In A User Selected Range
Originally Posted by westconn1
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|