Sleeper: Problem in coding

[hylw]

Hello, i face some problem in the following coding :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
testArrange Target.row, Target.Column
End Sub
 
Public Sub testArrange(ByVal lRow As Integer, ByVal iCol As Integer)
......
......
If lRow >= startRow1ER(c) And lRow < curRow2ER Then
If Cells(lRow, iCol).Formula <> vbNullString Then
Cells(curRow1ER, curCol).Formula = Cells(lRow, iCol).Formula
Cells(curRow1ER, curCol).BorderAround ColorIndex:=22, Weight:=xlMedium
Cells(curRow1ER, curCol).WrapText = True
Cells(curRow1ER, curCol).VerticalAlignmenr = xlCenter
* Cells(curRow1ER, curCol).HorizontalAlignment = xlJustify
Cells(lRow, iCol).Delete
Cells(curRow2ER, iCol).Insert
Cells(lRow, iCol - 1).Select
curRow1ER = curRow1ER + 1
curRow2ER = curRow2ER - 1
row1ItemER(c) = row1ItemER(c) + 1
row2ItemER(c) = row2ItemER(c) - 1
End If
End If
......
......
End Sub

The Sub testArrange only execute until "*" , but if i change it as following, it work correctly:

If lRow >= startRow1ER(c) And lRow < curRow2ER Then
If Cells(lRow, iCol).Formula <> vbNullString Then
Cells(curRow1ER, curCol).Formula = Cells(lRow, iCol).Formula
Cells(lRow, iCol).Delete
Cells(curRow2ER, iCol).Insert
Cells(lRow, iCol - 1).Select
curRow1ER = curRow1ER + 1
curRow2ER = curRow2ER - 1
row1ItemER(c) = row1ItemER(c) + 1
row2ItemER(c) = row2ItemER(c) - 1
Cells(curRow1ER - 1, curCol).BorderAround ColorIndex:=22, Weight:=xlMedium
Cells(curRow1ER - 1, curCol).WrapText = True
Cells(curRow1ER - 1, curCol).VerticalAlignmenr = xlCenter
Cells(curRow1ER - 1, curCol).HorizontalAlignment = xlJustify
End If
End If

Why? please advise. Thanks

[Bob Phillips]

Bit hard to say as there is so much missing. What is the error, how are all those variables set and to what, etc.

[hylw]

Thanks for reply . I 'll try to explain more.

I am code to trigger event when click on the cells. The content of the selected cell will move to certain Range, the current selected cell will be deleted and a cell will be inserted . (For example ,the 1st clicked cell's content will move to Range("G5"), 2nd clicked cell's content will move to Range("G6") ,3rd will move to Range("G7") and forward...)

My coding is as below (code that can work):

Public Sub testArrange(ByVal lRow As Integer, ByVal iCol As Integer)
......
......
 
'trigger event if in certain col
If iCol = curCol + 2 Then  
'First part:
'trigger event if in certain row
If lRow < curRow2 Then
 If Cells(lRow, iCol).Formula <> vbNullString Then
    Cells(curRow1, curCol).Formula = Cells(lRow, iCol).Formula
    Cells(curRow1, curCol).BorderAround ColorIndex:=17, Weight:=xlMedium
    Cells(curRow1, curCol).WrapText = True
    Cells(curRow1, curCol).VerticalAlignment = xlCenter
    Cells(curRow1, curCol).HorizontalAlignment = xlJustify
    curRow1 = curRow1 + 1
    curRow2 = curRow2 - 1
'move the selected cell to another place so it won't trigger event 
    Cells(lRow, iCol - 1).Select     
    Cells(lRow, iCol).Delete                   'delete selected cell
    Cells(curRow2, iCol).Insert              'insert a new cell
  End If
End If
'Second part:
'trigger event if in certain row
If lRow >= startRow1TS(c) And lRow < curRow2TS Then
     If Cells(lRow, iCol).Formula <> vbNullString Then
        Cells(curRow1TS, curCol).Formula = Cells(lRow, iCol).Formula
        Cells(curRow1TS, curCol).BorderAround ColorIndex:=33, Weight:=xlMedium
        Cells(curRow1TS, curCol).WrapText = True
        Cells(curRow1TS, curCol).VerticalAlignment = xlCenter
        Cells(curRow1TS, curCol).HorizontalAlignment = xlJustify
        curRow1TS = curRow1TS + 1
        curRow2TS = curRow2TS - 1
        row1ItemTS(c) = row1ItemTS(c) + 1
        row2ItemTS(c) = row2ItemTS(c) - 1
        Cells(lRow, iCol - 1).Select
        Cells(lRow, iCol).Delete      
        Cells(curRow2TS, iCol).Insert
    End If
End If
'Third part:
'trigger evnet if in certain row
If lRow >= startRow1ER(c) And lRow < curRow2ER Then
    If Cells(lRow, iCol).Formula <> vbNullString Then
       Cells(curRow1ER, curCol).Formula = Cells(lRow, iCol).Formula
       Cells(lRow, iCol).Delete
       Cells(curRow2ER, iCol).Insert
       Cells(lRow, iCol - 1).Select
       curRow1ER = curRow1ER + 1
       curRow2ER = curRow2ER - 1
       row1ItemER(c) = row1ItemER(c) + 1
       row2ItemER(c) = row2ItemER(c) - 1
       Cells(curRow1ER - 1, curCol).BorderAround ColorIndex:=22, Weight:=xlMedium
       Cells(curRow1ER - 1, curCol).WrapText = True
       Cells(curRow1ER - 1, curCol).VerticalAlignmenr = xlCenter
       Cells(curRow1ER - 1, curCol).HorizontalAlignment = xlJustify
    End If
End If
End If
End Sub

The first and second part can work with the same coding pattern but when come to third part it fail unless being modify.

Is it clear enough? i hope u will get what i m trying to say. Thanks

[BlueCactus]

[ Welcome to VBAX, hylw. I took the liberty of reformatting your code for you. You can do this yourself by clicking on the VBA button in the post editor toolbar when writing your posts. Good luck with your question! ]

[Norie]

Why are you deleting/inserting cells can you just clear the contents of the cell?

[hylw]

Hi, thanks to all.

To Norie, i didn't use clear content because i need to shift the cell (which may have content) below the selected cell (which i going to delete) up to replace the selected cell. Thanks for your suggestion.