Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




Worksheet_Calculate
Range("B8:B37")) are formulas returning strings such as "Cash" or Equities", as two examples, then data is copied to a certain sheet for that string. There are about thirteen different sheets/strings.
This Select Case fails because Target is the entire range of B8:B37. How do I zero in on the cell that just calculated to make my data transfer correctly? I have to assume that any of the strings will appear more than once within the B8:B37 cell range. So if a calculate event occurs and returns "Cash" to a cell, there may be one or more strings "Cash" already in the range. I only want the cell that just recalculated. And if more than one cell changes with a calculate event then it seems none of this will work. Thanks. Howard Private Sub Worksheet_Calculate() Dim Target As Range Set Target = Range("B8:B37") If Not Intersect(Target, Range("B4:B37")) Is Nothing Then Select Case Target.Value Case Is = "Cash" 'Target.Resize(1, 5).Copy Sheets("Aug 2016 Cash Journal").Range("A" & Rows.Count).End(xlUp)(2) Target.Resize(1, 5).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2) Case Is = "Equity" 'Target.Resize(1, 5).Copy Sheets("Aug 2016 Equity Journal").Range("A" & Rows.Count).End(xlUp)(2) Target.Resize(1, 5).Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2) Case Else 'MsgBox "No Copy" End Select End If End Sub 
#2
Posted to microsoft.public.excel.programming




Worksheet_Calculate
Hi Howard,
Am Sat, 6 Aug 2016 22:20:45 0700 (PDT) schrieb L. Howard: Range("B8:B37")) are formulas returning strings such as "Cash" or Equities", as two examples, then data is copied to a certain sheet for that string. There are about thirteen different sheets/strings. This Select Case fails because Target is the entire range of B8:B37. How do I zero in on the cell that just calculated to make my data transfer correctly? I have to assume that any of the strings will appear more than once within the B8:B37 cell range. So if a calculate event occurs and returns "Cash" to a cell, there may be one or more strings "Cash" already in the range. I only want the cell that just recalculated. please post the formula for that range. Regards Claus B.  Windows10 Office 2016 
#3
Posted to microsoft.public.excel.programming




Worksheet_Calculate
please post the formula for that range. Regards Claus B.  They are VLOOKUP, but I don't have the exact formulas. I now expect to see a workbook example soon. Will post back then. Thanks, Howard 
#4
Posted to microsoft.public.excel.programming




Worksheet_Calculate
Hi Howard,
Am Sun, 7 Aug 2016 00:58:10 0700 (PDT) schrieb L. Howard: They are VLOOKUP, but I don't have the exact formulas. try it with the Worksheet_Change event and refer to the cell that do a changing in the value of the formula. Better help when I know the formula and the sheets layout. Regards Claus B.  Windows10 Office 2016 
#5
Posted to microsoft.public.excel.programming




Worksheet_Calculate
On Sunday, August 7, 2016 at 1:17:33 AM UTC7, Claus Busch wrote:
Hi Howard, Am Sun, 7 Aug 2016 00:58:10 0700 (PDT) schrieb L. Howard: They are VLOOKUP, but I don't have the exact formulas. try it with the Worksheet_Change event and refer to the cell that do a changing in the value of the formula. Better help when I know the formula and the sheets layout. Regards Claus B.  Hi Claus, Indeed. That appears to be the case, and the cell is one column removed from the formula. Turns out no VLOOKUP but a long, long IF/OR formula. I am using a VLOOKUP and referring to the lookup_value cell (where the change takes place)and that looks to be doing the trick. Sorry, should have had more info before posting. Thanks. Howard 
#6
Posted to microsoft.public.excel.programming




Worksheet_Calculate
Hi Howard,
Am Sun, 7 Aug 2016 02:22:59 0700 (PDT) schrieb L. Howard: Indeed. That appears to be the case, and the cell is one column removed from the formula. Turns out no VLOOKUP but a long, long IF/OR formula. I am using a VLOOKUP and referring to the lookup_value cell (where the change takes place)and that looks to be doing the trick. or try it this way: Private Sub Worksheet_Calculate() Dim varData As Variant Dim i As Long varData = Range("B8:F37") For i = LBound(varData) To UBound(varData) If varData(i, 1) = "Cash" Then Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2) _ .Resize(1, UBound(varData, 2)).Value = Application.Index(varData, i, 0) ElseIf varData(i, 1) = "Equity" Then Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp)(2) _ .Resize(1, UBound(varData, 2)).Value = Application.Index(varData, i, 0) End If Next End Sub Workbook_Calculate doesn't work with target. Regards Claus B.  Windows10 Office 2016 
#7
Posted to microsoft.public.excel.programming




Worksheet_Calculate
or try it this way: Private Sub Worksheet_Calculate() Dim varData As Variant Dim i As Long varData = Range("B8:F37") For i = LBound(varData) To UBound(varData) If varData(i, 1) = "Cash" Then Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2) _ .Resize(1, UBound(varData, 2)).Value = Application.Index(varData, i, 0) ElseIf varData(i, 1) = "Equity" Then Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp)(2) _ .Resize(1, UBound(varData, 2)).Value = Application.Index(varData, i, 0) End If Next End Sub Workbook_Calculate doesn't work with target. Regards Claus B.  Thanks Claus, I'll give that a go. Howard 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
worksheet_calculate  Excel Programming  
worksheet_calculate  Excel Programming  
Worksheet_Calculate  Excel Programming  
Worksheet_calculate()  Excel Programming  
worksheet_calculate **help**  Excel Programming 