Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Junp start my Select Case Macro

Working with cells Range("C4:C9")'
So: if C4 = "E" then return Sheets("Sheet2").Range("C5")
if C4 = "G" then return Sheets("Sheet2").Range("D5")

and so on with S and N.

Then the same for cell C5 except I will go to Sheet3("C5")for E, G, S, N.

If you can jump start me proper coding with C4 and C5 I believe I can get the rest of the cells, C6, C7, C8 & C9.

Where each cell will have a different sheet to return from.

Thanks,
Howard


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C4:C9")) Is Nothing Then Exit Sub

Select Case Target.Value

Target = "E"
Target.Offset(, 1) = Sheets("Sheet2").Range("C5")

Target = "G"
Target.Offset(, 1) = Sheets("Sheet2").Range("D5")

Target = "S"
Target.Offset(, 1) = Sheets("Sheet2").Range("E5")

Target = "N"
Target.Offset(, 1) = Sheets("Sheet2").Range("F5")

Target = ""

End Select

End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Junp start my Select Case Macro

Hi Howard,

Am Tue, 22 Apr 2014 23:48:09 -0700 (PDT) schrieb L. Howard:

Working with cells Range("C4:C9")'
So: if C4 = "E" then return Sheets("Sheet2").Range("C5")
if C4 = "G" then return Sheets("Sheet2").Range("D5")

and so on with S and N.


try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C4:C9")) Is Nothing Or _
Target.Count 1 Then Exit Sub

Dim adr As String

Select Case UCase(Target.Value)
Case "E"
adr = "C5"
Case "G"
adr = "D5"
Case "S"
adr = "E5"
Case "N"
adr = "F5"
End Select

If Len(adr) 0 Then
Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)
End If
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Junp start my Select Case Macro

On Wednesday, April 23, 2014 12:28:19 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Tue, 22 Apr 2014 23:48:09 -0700 (PDT) schrieb L. Howard:



Working with cells Range("C4:C9")'


So: if C4 = "E" then return Sheets("Sheet2").Range("C5")


if C4 = "G" then return Sheets("Sheet2").Range("D5")




and so on with S and N.




try:



Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C4:C9")) Is Nothing Or _

Target.Count 1 Then Exit Sub



Dim adr As String



Select Case UCase(Target.Value)

Case "E"

adr = "C5"

Case "G"

adr = "D5"

Case "S"

adr = "E5"

Case "N"

adr = "F5"

End Select



If Len(adr) 0 Then

Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)

End If

End Sub





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional


Thanks Claus, that should get me going.

Howard
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Junp start my Select Case Macro


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C4:C9")) Is Nothing Or _

Target.Count 1 Then Exit Sub



Dim adr As String



Select Case UCase(Target.Value)

Case "E"

adr = "C5"

Case "G"

adr = "D5"

Case "S"

adr = "E5"

Case "N"

adr = "F5"

End Select



If Len(adr) 0 Then

Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)

End If

End Sub
Regards

Claus B.

--


Claus, I am stunned!!!

That "If - Then" statement at the bottom is brilliant! Took me a bit to digest it. I was wondering how in the world you knew I had 6 sheets to draw from for my offset returns.

There is nothing more to fill out.

Thanks again.

Howard

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Junp start my Select Case Macro

Hi Howard,

Am Wed, 23 Apr 2014 01:29:46 -0700 (PDT) schrieb L. Howard:

That "If - Then" statement at the bottom is brilliant! Took me a bit to digest it. I was wondering how in the world you knew I had 6 sheets to draw from for my offset returns.


you wrote it in your explanation:

"Then the same for cell C5 except I will go to Sheet3("C5")for E, G, S,
N.
If you can jump start me proper coding with C4 and C5 I believe I can
get the rest of the cells, C6, C7, C8 & C9.
Where each cell will have a different sheet to return from."



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Junp start my Select Case Macro


I see, guess I explained it better than I thought.

Can you show me how I could do the same for the Range("C13:C18") within the same change event macro.

This relates to evaluating people and there will be several more ranges going down C column. I hoping if I have an example of two I can do the rest myself, but not getting anywhere in my attempts so far.

I understand this would have to be adjusted to -11 for row 13

Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)

but the rest is giving me fits.

Howard
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Junp start my Select Case Macro

Hi again,

Am Wed, 23 Apr 2014 03:58:06 -0700 (PDT) schrieb L. Howard:

I understand this would have to be adjusted to -11 for row 13

Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)


then try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C4:C9, C13:C18")) Is Nothing Or _
Target.Count 1 Then Exit Sub

Dim adr As String

Select Case UCase(Target.Value)
Case "E"
adr = "C5"
Case "G"
adr = "D5"
Case "S"
adr = "E5"
Case "N"
adr = "F5"
End Select

If Len(adr) 0 Then
If Target.Row < 10 Then
Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)
Else
Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)
End If
End If
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Junp start my Select Case Macro

On Wednesday, April 23, 2014 4:22:45 AM UTC-7, Claus Busch wrote:
Hi again,



Am Wed, 23 Apr 2014 03:58:06 -0700 (PDT) schrieb L. Howard:



I understand this would have to be adjusted to -11 for row 13




Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)




then try:



Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C4:C9, C13:C18")) Is Nothing Or _

Target.Count 1 Then Exit Sub



Dim adr As String



Select Case UCase(Target.Value)

Case "E"

adr = "C5"

Case "G"

adr = "D5"

Case "S"

adr = "E5"

Case "N"

adr = "F5"

End Select



If Len(adr) 0 Then

If Target.Row < 10 Then

Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)

Else

Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)

End If

End If

End Sub





Regards

Claus B.


Thanks Claus, I'll see how many evaluation blocks I can add before I crash. I'm thinking I can use statements like < and/or to define more than two evaluation blocks.

Howard
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Junp start my Select Case Macro

Hi Howard,

Am Wed, 23 Apr 2014 09:19:22 -0700 (PDT) schrieb L. Howard:

Thanks Claus, I'll see how many evaluation blocks I can add before I crash. I'm thinking I can use statements like < and/or to define more than two evaluation blocks.


you can insert a Select Case into the
If Len(adr) 0 Then statement:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C4:C9, C13:C18")) Is Nothing Or _
Target.Count 1 Then Exit Sub

Dim adr As String
Dim Dif As Long

Select Case UCase(Target.Value)
Case "E"
adr = "C5"
Case "G"
adr = "D5"
Case "S"
adr = "E5"
Case "N"
adr = "F5"
End Select

If Len(adr) 0 Then
Select Case Target.Row
Case Is < 10
Dif = 2
Case Is < 20
Dif = 11
End Select

Target.Offset(, 1) = Sheets(Target.Row - Dif).Range(adr)

End If
End Sub



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Junp start my Select Case Macro

<FWIW
I prefer to put processing code in a standard module that can be
'called' from any sheet that may need to use that same code. In this
case I'd probably do something like...

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C4:C9")) Is Nothing _
Or Target.Count 1 Then Exit Sub
Call XferSheetData(Me, Target, Sheets(Target.Row - 2))
End Sub


In a standard module:

Sub XferSheetData(wksSrc As Worksheet, _
rngSrc As Range, wksTgt As Worksheet)
Dim sAddr$

Select Case UCase(wksSrc.rngSrc.Value)
Case "E": sAddr = "C5"
Case "G": sAddr = "D5"
Case "S": sAddr = "E5"
Case "N": sAddr = "F5"
End Select

If sAddr < "" Then wksSrc.rngSrc.Offset(0, 1) = wksTgt.Range(sAddr)
End Sub

I might not include the actual transfer code (last executable line
above) in this procedure if I configure it as a function to return the
ref cell address. (Of course, you do know a simple LOOKUP function
would get the job done without need for VBA! Right?<g)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion




  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Junp start my Select Case Macro

On Wednesday, April 23, 2014 9:32:06 AM UTC-7, Claus Busch wrote:
Hi Howard,



Am Wed, 23 Apr 2014 09:19:22 -0700 (PDT) schrieb L. Howard:



Thanks Claus, I'll see how many evaluation blocks I can add before I crash. I'm thinking I can use statements like < and/or to define more than two evaluation blocks.




you can insert a Select Case into the

If Len(adr) 0 Then statement:



Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C4:C9, C13:C18")) Is Nothing Or _

Target.Count 1 Then Exit Sub



Dim adr As String

Dim Dif As Long



Select Case UCase(Target.Value)

Case "E"

adr = "C5"

Case "G"

adr = "D5"

Case "S"

adr = "E5"

Case "N"

adr = "F5"

End Select



If Len(adr) 0 Then

Select Case Target.Row

Case Is < 10

Dif = 2

Case Is < 20

Dif = 11

End Select



Target.Offset(, 1) = Sheets(Target.Row - Dif).Range(adr)



End If

End Sub







Regards

Claus B.



Here's what I did, which works.

If Len(adr) 0 Then
If Target.Row < 10 Then
Target.Offset(, 1) = Sheets(Target.Row - 2).Range(adr)

ElseIf Target.Row 12 And Target.Row < 19 Then
Target.Offset(, 1) = Sheets(Target.Row - 11).Range(adr)

ElseIf Target.Row 21 And Target.Row < 28 Then
Target.Offset(, 1) = Sheets(Target.Row - 20).Range(adr)
End If
End If


I like the Len(adr) select case.

In your example it seems both cases would be true if the Row was greater than 20. So would I use a and a < to 'capture' the rows?

Have not tried it, will do so now.

Thanks for the advice.

Howard

  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Junp start my Select Case Macro



Have not tried it, will do so now.



Thanks for the advice.



Howard


Hi Claus,

I found this to work well.

If Len(adr) 0 Then

Select Case Target.Row
Case 4 To 9
Dif = 2
Case 13 To 18
Dif = 11
Case 22 To 27
Dif = 20
End Select

Target.Offset(, 1) = Sheets(Target.Row - Dif).Range(adr)
End If

Thanks.
Howard
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Junp start my Select Case Macro

Hi Howard,

Am Wed, 23 Apr 2014 14:23:39 -0700 (PDT) schrieb L. Howard:

Select Case Target.Row
Case 4 To 9


yes, that solution is more reliable


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
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
Case not recognized in Select Case kevlarmcc Excel Programming 4 March 29th 10 07:40 PM
Select....Case macro in Excel 2007 ManhattanRebel Excel Programming 3 July 5th 08 05:13 PM
Case without Select Case error problem Ayo Excel Discussion (Misc queries) 2 May 16th 08 03:48 PM
Calling Macro when using Select Case helmekki[_49_] Excel Programming 1 October 30th 04 12:22 AM


All times are GMT +1. The time now is 07:31 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"