Instead of UDF I need to develop Sub in Personal.XLSB

B

Bamerand

Board Regular
Joined
Jan 11, 2013
Messages
62
A question to clarify...

I need to perform calculation in a particular cell that is located in a row (for example, in the table I provided here, it is "W1"), where variables are changed by value, fill and font color. I have a set of table (B2:V20) with values populated such as "11" and I need to calculate when I change values in either one or more rows. I cannot use "=SUM()" function for the values are to be calculated based on the font color, cells interior color, fill etc. Before I used UDF functions that made computations based on font color and the cells interior color. But because of huge amount of data, the computation goes a bit difficult, i.e. by changing 1 or 2 or more value, the UDF calculate the entire sheet / workbook.

In order to save time and avoid calculating the whole sheet / workbook, I need to come with a procedure, not function, but will calculate / sum the row, where change took place only.Here is a sample of the code I used to calculations. I figured how to use PERSONAL.XLSB and store the code in the personal book and call it via buttons assigned via MS Excel menu.

I need to redo the following "Function ORT" into "Procedure ORT"

Function ORT(Rng As Range) As Long
Dim Cell As Range Application.Volatile
For Each Cell In Rng If Cell.Font.Color = 16711680 And Cell.Value = ("11") And Cell.Font.Bold = True _
And (Cell.Interior.Color = (13434828)) Then ORT = ORT + 1 Next Cell
End Function

I assume that changing Function ORT to Procedure ORT, plus code Application.Run "Personal.xlsb!Function ORT", which will run this piece of code

Private Sub MyMacro1()
Application.ScreenUpdating = False
Selection.Cells.Font.Color = 16711680
Selection.Cells.Value = ("11")
Selection.Cells.Font.Bold = True
Selection.Cells.Interior.Color = 13434828
Application.CalculateFull
Application.Run "Personal.xlsb!Function ORT"
Application.ScreenUpdating = True
End Sub

Now here come the hardest part - the code needs to calculate only the selected / changed rows, i.e. if I make changes in 1st row, then it should calculate the first row changes, if I do 1st and 2nd rows, then the code must calculate 1st and second rows. I believe there is a way to use ByVal Target As Range, but I am not sure how to use it (gave a try, no luck).Can anybody give me a clue on how to do it? Any links, articles or posts are welcome, sharing your thoughts in particular...

Regards,

Bamerand

(formating of the table is slightly different from the code specified, but that should go without saying)....

BCDEFGHI JKLMNOPQRSTUVW
111111111111111111111111111111111111111111155
2111111111111111111111111111111111111111111
3111111111111111111111111111111111111111111
4111111111111111111111111111111111111111111
5111111111111111111111111111111111111111111
6111111111111111111111111111111111111111111
7111111111111111111111111111111111111111111
8111111111111111111111111111111111111111111
9111111111111111111111111111111111111111111
10111111111111111111111111111111111111111111
11111111111111111111111111111111111111111111
12111111111111111111111111111111111111111111
13111111111111111111111111111111111111111111
14111111111111111111111111111111111111111111
15111111111111111111111111111111111111111111
16111111111111111111111111111111111111111111
17111111111111111111111111111111111111111111
18111111111111111111111111111111111111111111
19111111111111111111111111111111111111111111

<colgroup><col><col span="21"><col></colgroup><tbody>
</tbody>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Try this

Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("b24:B100,e24:e100")) Is Nothing Then 'direct the range you want to monitor
Sheets("reference lookup").Range("o4") = Target.Cells 'I use this to pick up the value under the cursor and place on a sheet
Selection.NumberFormat = "General"
End If
Exit Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Userform fails to set focus on textbox
Replies
5
Views
382
breynolds0431
B
Q
  • Question
VBA help I need to combine two codes
Replies
1
Views
306
Fluff
Fluff
deadlyjack
  • Solved
Looping through A3-A13 with monthly numbers (Formulas only)
Replies
6
Views
556
deadlyjack
deadlyjack
S
  • Solved
Need help modifying an Excel VBA coded to add a check for new hires less that 90 days.
Replies
1
Views
245
spittingfire
S
Livin404
  • Question
Offset interior color of a blank cell if adjacent cells have a value.
Replies
5
Views
210
DanteAmor
DanteAmor

Forum statistics

Threads
1,203,051
Messages
6,053,219
Members
444,648
Latest member
sinkuan85

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