Strange formatting problem

[niall91]

Hi guys

I have a very strange problem. To fill out this data i use a worksheet change event. For some reason the code (LHS-1) on line one is offset from the code (LHS-2) below it and this is causing other problems when i try to run formulas which might use this code. Its only line one that has this problem and all cells are set to general formatting. When i drag and fill from the cell below it, it aligns correctly and formulas work then but when new data is entered and the worksheet change event is triggered its the same problem again. Note that the code that fills line one also fills other lines so i don't think is a problem with the code but its also the event that causes it so this is why its confusing.

 

[RoryA]

What's the code?

 

[niall91]

Thanks for your interest

If Target.Column = 13 And Cells(trow, 13) = "" Then

On Error GoTo xit

Else

Dim code As String

Dim fnd As String

'Qualifer to check for Door frame

fnd = Cells(trow, 13)

fnd = InStr(fnd, "DF")

If fnd = 1 Then

fnd = Cells(trow, 13) 'extract the door frame code

fnd = Mid(fnd, 3, 1) 'extract the number

If fnd > 4 Then

MsgBox "Inproper Door Frame Code"

Cells(trow, 13) = ClearContents

Cells(trow, 13).Select

End If

'1doorframe sets, 1door, 2 door, 3door and 4 door

If Target.Column = 13 And Target = "DF" & fnd Then

OptimizeVBA True

'Left stile info

Cells(trow, 18) = Cells(trow, 10).Value 'OTY

Cells(trow, 19) = Cells(trow, 11).Value 'Height

Cells(trow, 20) = ls 'Stile width

code = "LHS-" & getU

Cells(trow, 21) = code

'right Stile info

Cells(trow, 22) = Cells(trow, 10).Value 'OTY

Cells(trow, 23) = Cells(trow, 11).Value 'Height

Cells(trow, 24) = rs 'Stile width

code = "RHS-" & getU

Cells(trow, 25) = code

'Top rail info

Cells(trow, 26) = Cells(trow, 10).Value 'OTY

Cells(trow, 27) = Cells(trow, 12).Value - (ls + rs) 'Width

Cells(trow, 28) = tr 'Stile width

code = "TR-" & getU

Cells(trow, 29) = code

'Bottom rail info

Cells(trow, 30) = Cells(trow, 10).Value 'OTY

Cells(trow, 31) = Cells(trow, 12).Value - (ls + rs) 'Width

Cells(trow, 32) = br 'Stile width

code = "BR-" & getU

Cells(trow, 33) = code

'Door info

Cells(trow, 34) = Cells(trow, 10).Value 'OTY

Cells(trow, 35) = Cells(trow, 11).Value - (tr + br) 'door height

Cells(trow, 36) = Cells(trow, 12).Value - (ls + rs) 'door width

code = "Do" & fnd & "-" & getU

Cells(trow, 37) = code

OptimizeVBA False

End If

End If



End If

 

[RoryA]

What is getU?

 

[niall91]

What is getU?

each line has a unit number (Unit 1 to Unit 30) beside where the sizes are entered. getU pulls the number from the unit and assigns it to the part code (LHS-1) which means 'left hand stile unit 1'

 

[RoryA]

I meant what's the code for it if it's a function, or the code that assigns it if it's a variable?

Also, just to check, when the cell looks wrong, what does LEN() return for it? 5 or 6?

 

[niall91]

Yes there was a space after the 1 in Unit 1 which was causing the problem.
thanks for your help