VBA code and merged cells

[jdluke6545]

I am needing a VBA code to unlock cells but when looking for an answer I came across a thread that said VBA and merged cells don't work.
Well I have merged cells and there is no way I can redo the sheet and not have merged cells.
is there any way to get VBA code to play nice with merged cells?
I can post images of my sheet that I need help with VBA code If anyone has a possible solution to this problem first.
Or I can try the XL2BB thing.... never used that before tho....

 

[jdluke6545]

Did you mean to put it here:

foundCell.Offset(0, 6).MergeArea.Locked = False

 

[Michael M]

Yes but if the first merged cell is "G"...it should be 7 not 6

 

[jdluke6545]

Right.... I don't know why I put a 6 there in the post, but in my code I did put 7.
But that didn't work either.
Now, there is other data above the area we are working on.
And there is other data below this area as well. And both, above and below this area have different merged cells.
Could this be affecting it?
Can we modify the code to just apply only a section of the sheet? Like only A35 to A85? for example...

Set searchRng = ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

Somewhere here maybe? change this a bit to only look in a certain range? not search the whole A1:A COLUMN...?

 

[Michael M]

Set searchRng = ws.Range("A35:A85")
I'd suggest uploading the workbook to dropbox or similar.. so we can have a closer look...I think we are not getting a clear picture of you data

 

[jdluke6545]

On my actual sheet where I have this, it starts at row 35 and continues down to row 71. The "Title Blocks" are in row 33. This is NOT formatted as a table by the way
But I think the code only need to look at rows 35 thru 71 and look at Column A in that area and make the changes to column G rows 35 thru 71.
I would love to upload the whole file, but there is some sensitive data. I would need to create a copy and remove a lot. Can be done tho..!
ok, let me make the change you showed...

 

[jdluke6545]

No luck...
This is the code I have now:

Sub FindAndUnlockRelated()

Dim searchRng As Range
Dim ws As Worksheet
Dim findStr As String
Dim foundCell As Range
Dim firstMatch As String
Dim findArr() As String
Dim i As Long

findArr = Split("NT,ST", ",")

Set ws = ActiveSheet

Set searchRng = ws.Range("A35:A71")

For i = LBound(findArr) To UBound(findArr)
findStr = Trim(findArr(i))
Set foundCell = Cells.Find(what:=findStr, LookIn:=xlFormulas, MatchCase:=False, lookat:=xlWhole)
firstMatch = foundCell.Address

Do While Not foundCell Is Nothing
Set foundCell = Cells.FindNext(foundCell)
foundCell.Offset(0, 7).MergeArea.Locked = False
If firstMatch = foundCell.Address Then Exit Do
Loop

Next i
End Sub

I guess i need to make a copy of this sheet, remove sensitive data, and upload it somewhere.
I have to leave for now, but when i get back i will get a copy made.

 

[Michael M]

My concern is that you have cells Merged horizontally AND vertically

 

[jdluke6545]

My concern is that you have cells Merged horizontally AND vertically

LOL!! Actually, no just horizontally in the target area.

 

[Michael M]

Well both codes supplied should work fine.....so there is some informationwe are missing

 

[jdluke6545]

Well both codes supplied should work fine.....so there is some informationwe are missing

Ok. I'll get a copy started. Right now I need to take some friends to the airport.