Defining Variables and VBA Conditional on Cell Value

[-Wayner-]

Good afternoon,

I am trying to write a piece of VBA code which will perform an action (clearing the contents of certain cells) but will only act in a range of cells based on the contents of another cell.

The basic code i am trying to use is as follows (as an extract):

Sheets("User Interface").Select

If Range("HRTSelect") = "Supergroup" Then k = 7
If Range("HRTSelect") = "Group" Then k = 10
If Range("HRTSelect") = "Upcs" Then k = 13

For j = 4 To 205
If Cells(j, k + 1) = Range("HRTSpecify") And Cells(j + 1, k) = "" Then
Cells(j, k).ClearContents

If Range("HRTSelect") = "Upcs" Then
Range(Cells(j, k + 2), Cells(j, k + 8)).ClearContents

End If
End If
Next j

This row keeps coming up as a run time error '1004' Application-defined or object defined error.

If Cells(j, k + 1) = Range("HRTSpecify") And Cells(j + 1, k) = "" Then

For what it's worth i have DIM j & k as integers at the beginning of the whole process.

I'm not sure if it's that line itself which is causing the issue, or the way i have tried to specify 'k' in the initial part of my code.

If anyone could give me a little bit of a steer as to what might be causing the issue i would be eternally grateful!

Thanks in advance!
Wayner

 

[Mike Blackman]

Hi,

Try changing the range part like so...

Sheets("MySheet").Range("HRTSpecify") .Value

Change the sheet name accordingly.

 

[lozzablake]

End ifs are in the wrong place:

For j = 4 To 205
If Cells(j, k + 1) = Range("HRTSpecify") And Cells(j + 1, k) = "" Then
      Cells(j, k).ClearContents
End If


If Range("HRTSelect") = "Upcs" Then
    Range(Cells(j, k + 2), Cells(j, k + 8)).ClearContents
End If

see if that helps

 

[Mike Blackman]

Maybe, depends on the order the Op wants it to work in, it may be ok.

With some indentation it becomes:

    Sheets("User Interface").Select

    If Range("HRTSelect") = "Supergroup" Then k = 7
    If Range("HRTSelect") = "Group" Then k = 10
    If Range("HRTSelect") = "Upcs" Then k = 13
    
    For j = 4 To 205
        If Cells(j, k + 1) = Range("HRTSpecify") And Cells(j + 1, k) = "" Then
            Cells(j, k).ClearContents
            If Range("HRTSelect") = "Upcs" Then
                Range(Cells(j, k + 2), Cells(j, k + 8)).ClearContents
            End If
        End If
    Next j

 

[lozzablake]

good point!

 

[-Wayner-]

Thanks for the feedback guys, much appreciated.

On the End If's i think they are where they need to be, as if the first statement is false then the second statement doesn't need to be evaluated (it only need to happen if the first is true).

I'm a little confused by which part you mean to add the sheets statement to, could you elaborate please.

Cheers!

 

[-Wayner-]

IF it helps i'm sure it must be something to do with how i have defined the variable k, as i have a very similar bit of code as part of the same procedure (occurring before this part without any issue):

For i = 1 To 2040
If Cells(i, 2) = Range("HRTSpecify") And Cells(i + 1, 2) = "" Then
    Range(Cells(i, 3), Cells(i, 54)).ClearContents
End If
Next i

This works, but doesn't have the 'k' variable. Is the way i have defined 'k' proper procedure (as in based on the contents of the cell i have called HRTselect)?