Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Worksheet_Calculate

On Sunday, August 7, 2016 at 1:17:33 AM UTC-7, 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
worksheet_calculate [email protected] Excel Programming 2 March 25th 09 02:37 PM
worksheet_calculate Libby Excel Programming 1 March 1st 08 04:45 PM
Worksheet_Calculate tmkkoservo Excel Programming 1 August 20th 07 04:08 PM
Worksheet_calculate() Alex Excel Programming 1 August 30th 05 10:09 PM
worksheet_calculate **help** tommyboy Excel Programming 2 June 29th 04 08:33 AM


All times are GMT +1. The time now is 12:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"