Moving cells with VBA on merged columns.

[Carl Clements]

Hi,

I have some code, as part of a Loop, that moves the Active cell right by 5 columns (ActiveCell.Offset(0, 5).Select)

However, it is moving it by 7 columns, and I'm assuming it is something to do with the merged cells I have.

For example, cells J9 to N9 are merged together, with J9 being a variable called 'StoreLocation'. When the above code to move along 5 is run, the cursor moves to Q9. However, 5 along should be O9. Cells Q9 to U9 are also merged cells.

Can anybody advise how I would move along from J9 to Q9?

The full code is below:

[D8].Select
Do Until Trim(ActiveCell.Value) = ""
Cusip = Trim(ActiveCell.Value)

If Len(Cusip) <> 9 Then

MsgBox "Please check the cusip is correct in cell " & ActiveCell.Address, vbInformation, "Cusip Incorrect"

End

Else

End If

ActiveCell.Offset(1, 1).Select


Do Until Trim(ActiveCell.Value) = ""

Account = Trim(ActiveCell.Value)
StoreCol = ActiveCell.Column

If Len(Account) <> 11 Then

MsgBox "Please check the Account is correct in cell " & ActiveCell.Address, vbInformation, "Account Incorrect"
End

Else

'Stores the cell location of the Account

StoreLocation = ActiveCell.Address

Call ZINTS

Cells(NewRow, StoreCol).Select

Call Data_Into_Spreadsheet

Range(StoreLocation).Select
ActiveCell.Offset(0, 5).Select


End If

Loop

ActiveCell.Offset(-1, 1).Select

Loop

MsgBox "Finished, please check for any errors", vbInformation, "Finished"
[A1].Select
End Sub

 

[Joe4]

Merged Cells and VBA don't play nicely together. Because of this, most programmers avoid using them. There is a way to get the look of Merged Cells without actually merging them. You may want to try removing the merging cells and doing this, and then your code should work OK.

Here is the link that shows you what to do: http://www.officearticles.com/excel/merged_cells_in_microsoft_excel.htm