| Home |
| Search |
| Today's Posts |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Establish if Offset RANGE contains text
But you do hate top posting!
Mik wrote: On 29 Apr, 13:23, Dave Peterson wrote: First, you may have noticed that most responders are top posters in this forum. (Yep, it's different here!). You may want to start doing the same. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myStr As String Dim HowMany As String Dim RngToCheck As Range Set Target = Target.Cells(1) 'just a single cell! 'just check the changes in a specific range 'I used A2:J2, change this to match what you need If Intersect(Target, Me.Range("A2:J2")) Is Nothing Then Exit Sub End If myStr = UCase(Target.Value) 'this probably would have been a good test in the other code, too. if mystr = "" then 'the cell has been cleared exit sub 'or something else???? end if Select Case myStr Case Is = UCase("man"): HowMany = 1 Case Is = UCase("Exp"): HowMany = 2 'and so on and so on Case Else HowMany = 0 End Select If HowMany = 0 Then 'something bad happened, right? Exit Sub 'or a warning or what?? End If Set RngToCheck = Target.Resize(HowMany, 1) If Application.CountA(RngToCheck) 1 Then MsgBox "Insufficient space!" Else MsgBox "Ok to continue" 'do you really want that? End If End Sub Mik wrote: <<Snipped Dave, This works great. Thanks. How would I perform the same task, if I were to re-label the Types? So, 'Type 1' becomes 'Man' 'Type 2' becomes 'Exp' 'Type 3' becomes 'Fac' etc... Can you shed any light on this? Thanks again. Mik -- Dave Peterson- Hide quoted text - - Show quoted text - Dave, Fantastic. Does exactly what i wanted. Appreciate your help. Mik -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to establish as assumption | Excel Worksheet Functions | |||
| Copy range using offset range value | Excel Programming | |||
| Excel cannot establish a DDE with WORD | Excel Discussion (Misc queries) | |||
| Problem with Range.Cells.Offset and Range.Cells( row + offset, column) | Excel Programming | |||
| Does anyone know how to establish a DDE with Peachtree Accounting. | Excel Programming | |||
Threaded Mode
