PDA

View Full Version : Sleeper: Problem in coding



hylw
06-15-2005, 09:50 PM
Hello, i face some problem in the following coding :bug: :


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? :dunno please advise. Thanks :bow:

Bob Phillips
06-16-2005, 01:46 AM
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
06-16-2005, 02:55 AM
Thanks for reply :hi: . I 'll try to explain more. :think:

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 :friends:

BlueCactus
06-16-2005, 07:25 AM
[ 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
06-16-2005, 07:40 AM
Why are you deleting/inserting cells can you just clear the contents of the cell?

hylw
06-16-2005, 06:05 PM
Hi, thanks to all. :hi:

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. :friends: