Hi all,

I have a worksheet with about 500 rows, each one containing two columns with calculations in it (some simple formulas like H4 + J4).

Now here's what I need to do:

If one of the two calculations within one row throws a new result, I have to fire some Macros on the same worksheet. That's all.

If I tried to use "Worksheet_Change()" function it worked great, because I can determine the accessed row (which IMHO is not given within the "Worksheet_Calculate()" function). Unfortunately this function does only work for direct user input and not for output cells...

I really just would like to know how this could be done in VBA:

Sub Worksheet_Calculate()

Dim calculatedRow As Integer

calculatedRow = ????

Call myMacro(calculatedRow)

End Sub

Is there any suggestion or am I completely wrong here?

Thanks in advance, Paul

I have a worksheet with about 500 rows, each one containing two columns with calculations in it (some simple formulas like H4 + J4).

Now here's what I need to do:

If one of the two calculations within one row throws a new result, I have to fire some Macros on the same worksheet. That's all.

If I tried to use "Worksheet_Change()" function it worked great, because I can determine the accessed row (which IMHO is not given within the "Worksheet_Calculate()" function). Unfortunately this function does only work for direct user input and not for output cells...

I really just would like to know how this could be done in VBA:

Sub Worksheet_Calculate()

Dim calculatedRow As Integer

calculatedRow = ????

Call myMacro(calculatedRow)

End Sub

Is there any suggestion or am I completely wrong here?

Thanks in advance, Paul

- Macro Find Last Row with partial value, copy, and insert below
- Find last row with data and autofit, bold and center align
- Comparing columns to find row with largest difference
- Find last row on worksheet
- Find all any row with a colored cell (color not specific) within worksheet
- Find all rows with certain value
- Find last Row of worksheet
- I ned somehelp Finding Last Row with Non-Empty Cell in multiple Columns range
- Find Last Row With Text String
- Automatically Inserting Rows with Separate Sheet Forumla
- Delete rows with 3 or more semicolons
- Delete rows with data greater than eight weeks of age.
- Hide all rows with ZERO value
- Excel Complex Calculations to alter some row numbers
- Finding last row till data is there and calculating
- USD $10.00 Macro that Auto Calculates Zscores with Multiple Criteria
- Looking up data in a cell in one worksheet and finding it in another worksheet...
- Calculate number of rows with data
- Sort Rows with Certain Criteria to Various Worksheets
- Findjjing the range that triggered worksheet calculate
- Batch calculations from two worksheets
- Find last non-blank row with a worksheet function
- Find Last Row With Data, Copy & Paste
- Ignoring a row in all worksheet calculations

after the macro performs what it already does on the CB Design sheet, go to Summary sheet, find last row that contains "CB", copy that row and insert it directly below itself.

When the macro button on INV Design sheet is pressed, I would like to add the following:

after the macro performs what it already does on INV Design sheet, go to Summary sheet, find last row that contains "INV", copy that row and insert it directly below itself.

I have searched for several hours on the excel forums with no luck. The main hurdle is, I am not looking for an exact value in the last cell (e.g. CB2), but rather just "CB" of "INV". Most of the solutions have been for specific cells or not to find last row with the partial value.

An example that lacks the copy function, but that I tried to modify and run as a test before incorporating it into the existing macros, but realized it is looking for an exact value is below:

Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row For i = iLastRow To 1 Step -1 If Cells(i, "B").Value = "CB1" Then Rows(i + 1).Insert End If Next i End Sub Sub Macro5()Any help would be greatly appreciated.

Pat

I want to be able to find the row with the largest lead time.

I also want to be able to calculate the average lead time.

the lengths of the columns will vary as data is added and removed.

how would i do this in excel?

I'm sure this is simple, i've just had enough of trying and failing

many thanks

Thanks

With Columns("A:I") LR = .Find("*", .Cells(1, 1), xlValues, xlPart, xlByRows, xlPrevious, False, False).Row End WithNow I want/need to tweak it so it only selects rows where the value in column "I" = 0. I'm pretty new to VB so I'm not sure the best way to do this. Any suggestions? Thanks!

Example:

VB:Any Suggestions?Range("A10").Select Selection.Copy Application.CutCopyMode = False ActiveCell.FormulaR1C1 = _ "=IF('Recap Report'!R[-3]C2="""","""",'Recap Report'!R[-3]C2)" Selection.Copy Range("A10:A56").Select [COLOR="Red"]>[/COLOR] ActiveSheet.PasteIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I need to get a range VBA code to find the last row with a non-empty cell in a Multiple Column Range, specifically from columns A to AT

So far I have tried this one in different versions, but the best it does is give me the last row in Column "A", even when I use AT or any other column.

Range("A10", Range("a65536").End(xlUp)).Activate

I have tried alos some version wit R1C1 format, but no luck.

Help Please

I have a column that has formulas in 10000 rows.

Most of the time only first 7000 rows will have data, other cells below will have "". I need to find the last row with real data, not "".

Is there a way to add a condition or check to the following line?

thanks,

modytrane

Problem;

I have two work sheets (A&B).

Worksheet A is used to enter data into the defined row of cells then produces a calculated output in a totals column.

Worksheet B is used to perform all the calculations for worksheet A's output. Calculations are set up on a row by row basis. For example, row 5 in worksheet A is directly dependent upon the formulas in row 5 of worksheet B.

So, the rows in worksheet A are directly aligned and connected to the calculation rows in worksheet B. All developed formulas in worksheet B must be kept hidden and not available to the user. Only the worksheet administrator has access to worksheet B's formulas. Therefore, worksheet B is password protected from all users.

I want to be able to insert "add" a row into worksheet A and have worksheet B's formula rows follow the change of inserting a row into worksheet A. This will allow the formulas in worksheet B to be directly connected on a row by row basis to worksheet A.

Right now if I add a row in worksheet A, I lose the formula connection in worksheet B and all calculated rows that reside underneath the newly inserted row in worksheet A are mis-calculated because the links are all messed up.

If needed, I can provide the Excel file.

Any ideas on this one??

Thank you for your help!

Jimbo73

Code:

Sub DeleteRowsContaining() Dim rng As Range Dim what As String what = ";" Do Set rng = ActiveSheet.UsedRange.Find(what) If rng Is Nothing Then Exit Do Else Rows(rng.Row).Delete End If Loop End SubThank you!

I am compiling a simple excel database that gets updated on a weekly bases, the volume of data is variable and not of fixed size. Whilst I have managed to get it fill by finding the next empty row, I am having trouble with deleting data over eight weeks of age. I have devised this code below to find the row with the date that is greater than eight weeks, and it deletes that row.

Sub SearchDate()

Dim gCell As Range

Set gCell = Columns(1).Find(DateValue(Range("a1")))

gCell.Select

Selection.Rows.Delete

End Sub

What I canâ€™t seem to be able to get right is it to delete all the rows above it up to and including row three which will hold this older data.

Thank you for any assistance you can provide

Simonw

Thanks in advance for your help!

I have an Excel spreadsheet, in which resides a very complex set of polynomials we use to calculate engineering values from data we receive from our equipment. These calculations utilize many different cells, and also some coefficient data which resides in an array/table within another worksheet in the same book. The spreadsheets works like this: a technician enters the data from our equipment into one cell, and the output from the calculation is spawned in another, but what I am attempting to do is have a column of data converted. I cannot seem to find a way to enter the first cell of data into the calculation and have the calculation produce the results in another cell, but through the range of the entire column. Basically what I need is cell A1 on Sheet1 to be entered into Cell D1 on the calculation sheet, and have the calculation sheet return the results to cell B1 in Sheet1...............easy enough, but how do I make it so Cells A2, A3, A4, etc, are entered into Cell D1on the calculation sheet with the calculation returning their respective outputs into cells B2, B3, B4, etc...........

Another way around this, that I suppose could work, would be for me to move the calculations into sheet1, and copy the calculation so that each row has it's own, but I have to point the calculations to the correct coefficient value (which will be the same for all rows) and when I do this and try to copy/paste special, Excel sequentially changes the numbers; can I prevent this in any way? An example:

I have this formula in A6:

='COE' !D47+J5*('COE' !D48+J5*('COE' !D49+J5*('COE' !D50)))

When I try to copy/paste special to the next row, I get this:

='COE' !D48+J5*('COE' !D49+J5*('COE' !D50+J5*('COE' !D51)))

I need this:

='COE' !D47+J6*('COE' !D48+J6*('COE' !D49+J6*('COE' !D50)))

So, I need the function to get the data from the same cells on the COE sheet, but sequentially alter the row cell from J5 to J6, J7, J8, etc............

I know this is a mouthful, and there are probably more efficient means to achieving then end results, so I appreciate any input you all may have to offer.

ST

I has a worksheet which gets data for a week range. My coloumns are fixed i.e one week range. But Rows can be differed and i need to calculate avg and based on avg i need to color the rows.

My format is like this :

A B C D E F G H

1

2

3

4

5

X

H is the coloumn where i take the avg. I have written VBA code by hard coding the row value, but in general it will vary. I am not sure how to find the last row and then calculate Avg.

I am adding the code which i did for constants Rows.

ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])" Range("H2").Select Selection.AutoFill Destination:=Range("H2:H90") Range("H2:H90").Select Columns("H:H").Select Selection.NumberFormat = "0" Dim Rng As Range, cell As Range Set Rng = Range("A2:G90") Rng.Interior.ColorIndex = xlNone For Each cell In Rng If cell.Value < Range("H" & cell.Row) * 0.2 Then cell.Interior.ColorIndex = 3 ElseIf cell.Value > Range("H" & cell.Row) * 0.2 And cell.Value < Range("H" & cell.Row) * 0.5 Then cell.Interior.ColorIndex = 6 End If Next cell

Could you please help me out in writing VBA code for getting last row and do the calculation instead of hardcoding the row range.

Thanks & Regards,

Pradeep

$10 (possibly more) for whoever can help me with this. As soon as possible would be wonderful. See attached spreadsheet for references.

I work in pediatric research at a hospital, and a few of the studies we are doing involve the patients getting a bone scan to measure density and mineral content. The scanning machine (DXA machine) allows us to extract the measurements (raw numbers, ex. 0.5457838), and paste them into Excel in the format seen in the MachineDataOutput worksheet. There are four different kinds of measurements I deal with (shown in D1, E1, F1, G1 of MachineDataOutput) Then, using the gender & age corresponding to the value, I take that measured value and type it into the "Measured" column cell of the corresponding table (based on gender and age) in the OldCalcSheet worksheet. Then, using the formulas embedded in the worksheet OldCalcSheet in the "Zscore" columns (formulas vary depending on which of the 4 types of data being calculated), a zscore is calculated- based on the mean and standard deviation which is specific to a gender and the age within that gender (see formula).

The problem I have is the tedious nature of the calculations- by hand, one at a time, when I have usually 200-300 rows of data to calculate. So why not create a macro that will do it for me?

This is what I need: A spreadsheet with a macro that will evaluate the data from MachineDataOutput and give me the zscore for each, in the next column over. Preferably with a click of a button that says "Calculate" or something. I have already done some code (probably all wrong) which stores all of the means and standard deviations for each age within a gender. It's all clearly labeled in the code. The "Whole Body Subtotal BMC" (OldCalcSheet) or "WB SUB_BMC" (MachineDataOutput) requires a third criteria for calculation, "CV(S)". Otherwise, the other three only need mean and standard deviation. Take a look at the spreadsheet attached, the formulas in the OldCalcSheet "zscores" columns, and my code I've started and let me know what you can do. I will consider paying more if it's done well. Thanks!

Thanks in advance for taking the time to look at my question and giving it some thought. I am trying to simplify and reduce some manual data input for a voluminous workbook that my company uses. It is a huge set of spreadsheets with 100's of contracts listed by "policy number" with various columns of different pieces of data regarding the contract specifics. What is currently done manually is the summation of earned premiums on one worksheet sorted by policy is calculated giving the total earned premiums to date for that specific policy. This number is then copy and pasted to another spreadsheet on the same workbook in a row with the corresponding policy number to then be used formulaically with other numbers in the row. What I am wondering is if there is any way to instead of individually clicking and linking the corresponding cells manually if some sort of formula could be used to lookup the policy number from one spreadsheet and find it in another and put the earned premiums total into the correct cell and row. I am not very familiar with formulas typically used in excel but was wondering if some sort of self written vlookup formula would be appropriate. Any advice or solutions would be greatly appreciated by both me (getting major bonus points) and the guys manually inputting the data. The process usually takes them several hours and I am trying to find them a solution to simplify this process. Thanks again for any help!

whether the number of rows filled (of column A) with data is 3 or more.

msg hello is printed.

is this syntax correct?

Code:

If workbook2.Sheets("Sheet1").CountA(A) = 3 Then Msgbox "Hello"

Of those 12 columns, A thru L, I need to separate them out into separate based on 20 distinct values that can appear (multiple times or none at all for each value) in column B. (Note: If it would make it easier, I can re-arrange to make Column A the column with the criteria to sort these by. It would be a pain, but I can do it.)

Based on what is in Column B, I need the workbook to be sorted into the distinct subset workbooks, pulling only columns B, C, and G from the original. (The rest of the columns are used elsewhere in the workbook for calculations, so I need them to stay in the "Main" sheet, but I don't need them in the subset workbooks.

I further need it to not only sort these into individual workbooks based on column B, but also to only grab those rows that have a set value ("2" in my workbook) in column J...even though column J is not itself one that needs to be copied over...it's only a criteria determining what is copied.

Can this be done? I know I can do it with a combination of If statement and Vlookup, but if I do it that way, then any rows that don't qualify to be sorted into a particular workbook instead show up blank, so my data comes out with blank row, blank row, blank row, row with data (it qualifed with the if/vlookup), blank row, blank row, data row, etc.

My "Main" sheet usually has somewhere around 1,000 rows, and I have 20 different sheets to sort it into. I have to do this weekly, so I'm not inclined to sort/filter/copy/paste every time I need the report published.

Any help will be greatly appreciated.

I can't post an example because I don't have the necessary permissions to install anything (including the program to display worksheets) onto my computer. I can send you an example if you'd like, as I know that's an incredibly complicated question...and more complicated with no example.

Thanks!

Calinda

I use VBA to populate 2 cells with values.

When these cells are populated two adjacent cells containing formulas

trigger the worksheet calculate event. I do not want this to happen.

I have tried using application.caller..

Select Case TypeName(Application.Caller)

Case "Range"

v = Application.Caller.Address

Case "String"

v = Application.Caller

Case "Error"

v = "Error"

Case Else

v = "unknown"

End Select

MsgBox "caller = " & v

But it always returns "Error"

Is there another way to find out what is triggering the worksheet

calculate?

To summarize, I need to automate various cells to be placed in a separate calculations worksheet so every row gets a value and that value is inserted into the original worksheet. Take a look at my spreadsheet and it should make sense. I put comments in the highlighted cells (little red triangle) so just hover and you'll see which cells need to be moved where.

The ultimate goal is to get the 'Max Beam Radius (ft)' column filled automatically.

Please, any help would save me so much time, as I can not figure this baby out.

James

Say I have a worksheet with a data entery range of A1 to A100. The user is

required to enter data from row 1 downwards and cannot leave blanks between

rows.

I want to return the value in the last comleted row by function. Any ideas?

For example that last row with a value may be in cell A20 of the A1:A100

range.

Rgds,

Bruce

I'm trying to find the last row which has a value (non-empty) in column B. I want to then copy the formatting, validation and formulas of that last row (Columns A - BA) and paste it into the next available row.

I'm not very good / quite new to VBA, so here is what I've managed to piece together so far, from reading online (here and elsewhere). The comments show what I think will be happening and where... so you can see what I've got so far and what I'm still working on.

Any code / links / tips are appreciated.

Sub NewRow() Option Explicit 'Declare variables; Dim ws As Worksheet Dim lastRowColB As Long Dim screenUpdateState, statusBarState, calcState, _ eventsState, displayPageBreakState As Boolean 'Get current state of various Excel settings; screenUpdateState = Application.ScreenUpdating statusBarState = Application.DisplayStatusBar calcState = Application.Calculation eventsState = Application.EnableEvents displayPageBreakState = ActiveSheet.DisplayPageBreaks 'Turn off some Excel functionality; Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False 'Unprotect the worksheet, and unhide all rows so that the 'new row can be "inserted"; ActiveSheet.Unprotect ws.Cells.EntireRow.Hidden = False 'Find the last row (lastR) which has a value in Column B; lastRowColB = Range("B65536").End(xlUp).Row 'Copy the formatting, formulas and validation for the row 'found in the step above; 'Paste the formatting, formulas and validation to 'the next available row; Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False 'Rehide all unused rows (lastrow+1) and re-enable protection; ActiveSheet.Protect 'Restore state; Application.ScreenUpdating = screenUpdateState Application.DisplayStatusBar = statusBarState Application.Calculation = calcState Application.EnableEvents = eventsState ActiveSheet.DisplayPageBreaks = displayPageBreaksState End Sub

I'm not even sure this can be done but any help or ideas would be appreciated