VBA Find cell address with text to use as range variable

[tjdickinson]

In a workbook with multiple sheets, I want to find the address of a cell containing the string "MIS", which will always be in a sheet in which cell A1 begins "P " (with a space after the letter P). I then want to save this address to a range variable and pass it to another sub which will set the value of every cell at that address on every sheet to "MIS".

Here's the code I have so far:

Sub misRef(misCell As Range)   ' the variable misCell is created as range in another sub which calls this sub

Dim xSh As Worksheet

For Each xSh In Worksheets   ' search every sheet in the workbook
    xSh.Select
    If Left(Range("A1"), 2) = "P " Then   ' if A1 starts with "P " (with a space after P) -- this sheet will always contain 'MIS' somewhere in it
        Set misCell = Range("C4:F5,B7:F8,B11:E12,B14:E15").Find("MIS")   ' search the range c4 (etc) for the string "MIS" and define the variable with the range
    End If
Next

End Sub

The first problem seems to be that misCell is defined as the contents of the cell, not the address. When I run Debug.Print misCell, it spits out:

MIS
1A1

But when I run Debug.Print misCell.Address it gives:

$E$5

Maybe this is fine, but I feel like the variable should be holding the range, not the value, and I'm not sure how to fix that.

The second problem is in using the variable to set the cell value in other sheets. If I use misCell.Value = "MIS", it redefines the variable as "MIS", even though it is supposed to be a range (and Range.Value normally sets what's in the cells of that range). I tried using With misCell and With misCell.Address, but that gave me errors. So, again, I'm not sure what I need to do here.

Thanks for your help!

 

[Fluff]

but I feel like the variable should be holding the range, not the value,

It is holding the range object, not the value, that's why Debug.Print misCell.Address returns the address.

This misCell.Value = "MIS" does not "redfine" the variable it simply puts "MIS" into the relevant cells(s)

 

[tjdickinson]

It is holding the range object, not the value, that's why Debug.Print misCell.Address returns the address.

This misCell.Value = "MIS" does not "redfine" the variable it simply puts "MIS" into the relevant cells(s)

Thanks for the reply, Fluff. The thing is that misCell.Value = "MIS" didn't put "MIS" into the cells, and I'm not sure why.

Momentarily I'm working round it with

Dim misCellRow As Long
Dim misCellCol As Long

misCellRow = misCell.Row
misCellCol = misCell.Column

Cells(misCellRow, misCellCol).Value = "MIS"

which does work.

misCell.Value = "MIS" didn't return any error message--it just didn't fill in the cells. Everything else runs correctly around it; the macro doesn't stop or anything like that. Any ideas what the issue could be?

 

[Fluff]

The MIS will be going into the last sheet that has A1 starting with "P " because that is where the missCell is range is.

 

[tjdickinson]

The MIS will be going into the last sheet that has A1 starting with "P " because that is where the missCell is range is.

Ah okay...so (forgive me for forgetting the correct terms at the moment) because it took the reference from (ex) sheet 3, it is locked into sheet 3 in the variable. (In the debugger, it printed $E$5, with the $, so that means it's a fixed position, right?) And what I need is for it to transform that reference into something that can be used on every sheet (so, without the $). Is that correct?

 

[Fluff]

No, the $ signs just lock the cell reference, the same as in xl. The Range Object is tied to the sheet it's on, however you can use

Range(misCell.Address).Value = "MIS"

 

[tjdickinson]

No, the $ signs just lock the cell reference, the same as in xl. The Range Object is tied to the sheet it's on, however you can use

Range(misCell.Address).Value = "MIS"

Oh, brilliant. So I was actually just dancing around the code I needed, trying basically every combination except the one I needed. Doh! Thanks for the help, Fluff, I really appreciate it.

 

[Fluff]

Glad to help & thanks for the feedback.