Strange formatting problem

niall91

New Member
Joined
Jul 21, 2020
Messages
28
Office Version
  1. 2019
Platform
  1. Windows
formatting problem.JPG


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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What's the code?
 

niall91

New Member
Joined
Jul 21, 2020
Messages
28
Office Version
  1. 2019
Platform
  1. Windows
Thanks for your interest (y)

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What is getU?
 

niall91

New Member
Joined
Jul 21, 2020
Messages
28
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,067
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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?
 
Solution

niall91

New Member
Joined
Jul 21, 2020
Messages
28
Office Version
  1. 2019
Platform
  1. Windows
Yes there was a space after the 1 in Unit 1 which was causing the problem.
thanks for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,122,587
Messages
5,597,034
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top