• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Expanding worksheet _Calculate range from single cell [SOLVED]

B

bnpdkh

Member
Hello Forum,
I found the code below on line and it works well when a single cell has been identified as below. I am trying to expand the range to be IJ9:IJ306. Ikeep getting a RUNTIME ERROR13 "type Mismatch" Any help would be very helpful


Code: 
Private Sub Worksheet_Calculate()
Static oldval
If Range("IJ147").Value <> oldval Then
    oldval = Range("IJ147").Value
    '
    Call Update
    '
End If
End Sub
 
You can set a whole range to a single value, but you can't compare a whole range to a single value. I assume it's crashing at the If statement?
If you need to check all the cells in IJ9:IJ306, then you can either loop through all the cells, or use the Find method.

What is your overall goal?
 
In that case, you'll need to use the change event, rather than calculate.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("IJ9:IJ306")) Is Nothing Then Exit Sub
If Target.Value <> oldval Then
    oldval = Target.Value
    Call Update
End If
End Sub
NOTE: This assumes that user is changing something in the specified range, and it's not just a range of formulas.
 
Luke, thanks for helping me out again., I think I am close to fixing this issue. To clarify, there is a drop down validation list in cells U9:U306. The VLOOKUP code below returns data based on what is selected in column U. Further explanation can be entered in Column W. In range IJ9:IJ306 the data from these two are combined. For some reason the cell will not expand to wrap text which is why I would like to call the code below but I get an alarm
Code: 
=IF(U9="","",VLOOKUP(U9,DELAYDEF,2)&". ")&W9

Can you see an issue with this code. Keep getting "Blockif without EndIf"

Code: 
Sub Expand_Rows(ByVal Target As Range)
If Intersect(Target, Range("IJ9:IJ306")) Is Nothing Then Exit Sub
If Target.Value <> oldval Then
    oldval = Target.Value
    Range("IJ9:IJ306").Select
    With Selection
        .WrapText = True
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
 
End With
 
End Sub
 
The 2nd If statement has no closing line. If we fix the indenting, it looks like this:
Code: 
Sub Expand_Rows(ByVal Target As Range)
If Intersect(Target, Range("IJ9:IJ306")) Is Nothing Then Exit Sub
If Target.Value <> oldval Then
    oldval = Target.Value
    Range("IJ9:IJ306").Select
    With Selection
        .WrapText = True
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
     End With

'NOTE: We are still indented a line. Need to have an End IF
End If

End Sub
 
Also, as a learning tidbit, you don't need to select items to interact with them. Rather than doing this:
Code: 
    Range("IJ9:IJ306").Select
    With Selection
you can modify the range directly via:
Code: 
    With Range("IJ9:IJ306")
 
Thanks Luke, especially for the learning tidbit. I am not sure what the problem is but This does not seem to be working, cells are not wrapping text. Is there another way to accomplish this. I swear this worked yesterday!!! I have checked everything to make sur it is accurate
 
In that case, let's add a line to resize the rows.
Code: 
    With Selection
        .WrapText = True
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .EntireRow.AutoFit 'New line
     End With
 
You must log in or register to reply here.
Back
Top