Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copying error in rows for sheet and correct in other sheet

0

Hello

I'm  confused  why  this  problem occures!

I  modified  the code  by  add two  columns  in B,C  for  sheets result,result1 (becuase I will fill  by  another macro )

so  the  code  should  start  from column D  from  row2 . it 's ok  for  RESULT sheet 

but RESULT1 sheet will copy  starting from row13 (should be  from row2) I  no  know  why ! . the  orginal file (first)  will work  without  any problem ,  but  I  cut part of  my  project  still copy starting from  row13  for  RESULT1 sheet. I  still  no  know  what's  the  reason !

for me  this  mystery .

short words :  just  start  copying  from row  2 for column D   and  ignore  copy  ITEMS from  userform as  I  disabled  because I  want populating date  in column A when fill column D  automatically(no  need ITEMS from userform)

Private Sub CommandButton5_Click()
    Dim i           As Long
    Dim sheetname   As String
    Dim lRw         As Long ' for Last Row
    ' count to 2 not 4 (since there are only two buttons at present)
    For i = 1 To 2
        With Me.Controls("OptionButton" & i)
            If .Value Then sheetname = .Caption: Exit For
        End With
    Next i
    ' check a option was selected
    If sheetname = "" Then
        MsgBox "Please select an option button first"
        Exit Sub
    End If
    With Worksheets(sheetname)
        ' loop down UserForm controls
        For i = 1 To 11
            ' see if a Code was set
            If Me.Controls("ComboBox" & i + 1).Text <> "" Then
                'if Code set, find last row in B
' change copy lastrow  from B to D
                lRw = .Cells(Rows.Count, 4).End(xlUp).Row
 ' no need this  line I  replace with  date by  I  put  procedure in sheet  module  when 'fill column D
                '.Cells(lRw + 1, 1).Value = Me.Controls("TextBox" & i)
            ' change copying data from D:J instead of A:H
                .Cells(lRw + 1, 4).Value = Me.Controls("ComboBox" & i + 1)
                .Cells(lRw + 1, 5).Value = Me.Controls("ComboBox" & i + 12)
                .Cells(lRw + 1, 6).Value = Me.Controls("ComboBox" & i + 23)
                .Cells(lRw + 1, 7).Value = Me.Controls("ComboBox" & i + 34)
                .Cells(lRw + 1, 8).Value = Me.Controls("TextBox" & i + 11)
                .Cells(lRw + 1, 9).Value = Me.Controls("TextBox" & i + 22)
                .Cells(lRw + 1, 10).Value = Me.Controls("TextBox" & i + 33)
                ' clear Qty and Total controls (at least)
                Me.Controls("TextBox" & i + 11) = ""
                Me.Controls("TextBox" & i + 33) = ""
                Else
                ' do nothing
             End If
          Next i
    End With
End Sub

I  hope  anybody  has answer about  this problem .

Answer
Discuss

Discussion

Hi Mussa 

Took a quick look and saw the issue but not the cause - very odd!

Will look later but you said you "cut part of  my  project" -  which code portions did you remove please? Just the bits you commented out in the CommandButton5 code?
John_Ru (rep: 6092) Feb 15, '23 at 8:08 am
Hi John,
I posted the  code  with bold  comments  and  bold numbers  for  the  columns.
I  hope  this  help
Mussa (rep: 48) Feb 15, '23 at 8:36 am
using chat.openai.com/chat It gives the following response: 
Based on the code you provided, there are a few things you might want to consider: Ensure that you have option buttons with the names "OptionButton1" and "OptionButton2" in your UserForm, since the code expects these names to exist. Check that your ComboBox controls have names such as "ComboBox1", "ComboBox2", etc., and are sequentially numbered from 1 to 11. If not, you may need to update the code to use the correct control names. Confirm that the sheet names displayed as the captions of the option buttons are valid sheet names in your workbook. Make sure that the code is placed in the correct event handler for the button (i.e., "CommandButton5_Click"). If you have already checked these things and are still encountering issues, please provide more details on what exactly is not working or any error messages you are seeing. This will help me provide more targeted assistance.    
Trimtab23 Feb 16, '23 at 11:15 am
Hi Trimtab and welcome to the Forum. 

Looks like AI missed the point this time. The issue was a well-tried code line seemed to behave very oddly. The reason had nothing to do with Option Buttons. I explained it (below) and the user is working with my solution. He may want to pursue your AI approach but I suspect not.
John_Ru (rep: 6092) Feb 16, '23 at 11:36 am
Add to Discussion

Answers

0
Selected Answer

Mussa

The problem on worksheet RESULT1 is that D2:D12 seem to be empty but they are not. To test this, click in cell D11  then enter this in VB Project Explorer's Intermediate window:

Debug.Print IsEmpty(Selection)

It will return FALSE (so there are non-printable characters or something in there), same for D12. Do it in D13 and you'll get TRUE so the last row variable lRw gets calculated as 12 and data is populated from row 13.

I don't know what the problem is (perhaps from something you did or copied) but a simple fix is to delete sheet RESULT1, copy sheet RESULT and rename the copy as RESULT1 then replace the SUPPLIER heading in D with CUSTOMER (not CUSTOMMER as in your file). The macro will work well for both sheets.

In your code above, you say:

 ' no need this  line I  replace with  date by  I  put  procedure in sheet  module  when 'fill column D
                '.Cells(lRw + 1, 1).Value = Me.Controls("TextBox" & i)

with refers to the event macro behind each sheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, [D:D]) Is Nothing Or Target.Count > 1 Then Exit Sub
    Target.Offset(, -3) = Date
End Sub

That adds the date when a cell in D changes but I suggest you do NOT do that since every cell written by your macro will trigger this macro (so it is called unnecessarily 6 times per row). Instead, just change that line to read:

 ' write the date directly to column A
                .Cells(lRw + 1, 1).Value = Date

Also I suggest you check the formatting in both sheets, e.g. column A is for dates but you also have columns B and C formatted as Date.

Hope this helps.

Discuss

Discussion

Hi John,
(perhaps from something you did or copied) 
impressive spotting !
This thing did not occur to me at all. Rather, I did not expect that this thing would happen with Excel Yes, you are absolutely right. Mostly I copy cells from one sheet of to another to quickly fill in the data instead of writing manually at the time. It is beyond doubt that these cells are not clean, so strange things appeared in some cells as if they were empty so  I  copy  from row13 and  copy and paste   from row2:row12  and  it's   fixed. Actually John this thing I don't have enough knowledge about to cause this problem so I told you it's a mystery because it's unfamiliar to me! .As for your suggestion regarding the date, its problem is that it fills the bottom cells even if no data is filled from D :J, so there is no need to fill in the date. But it seems that I have reached the solution by placing it after copying the data, because the idea depends on the fourth column, if it is filled in, then the date is filled in, otherwise there is no need for that .
so  I  put  this  line  and seem to   work.
.Cells(lRw + 1, 10).Value = Me.Controls("TextBox" & i + 33)
                If .Cells(lRw + 1, 4).Value <> "" Then Cells(lRw + 1, 1).Value = Date

would Allow me, to tell you that ,please? even though your comments may seem simple, they actually often make a profound difference.
thanks  very much  for  your  guiding and  time
Mussa (rep: 48) Feb 15, '23 at 3:20 pm
Mussa. Glad that helped and thanks for selecting my Answer.

Sorry but I'm not clear what you mean by "problem is that it fills the bottom cells even if no data is filled from D :J, so there is no need to fill in the date.".but the idea does rely on column D being filled. Therefore I can't see a problem with your revised code. Good luck.
John_Ru (rep: 6092) Feb 15, '23 at 4:46 pm
sorry  john !
about  your  suggestion should  work  without  problem . again  the  problem was also non-printable characters in some  cells  . now  the  cells   are  clean  and  everyting  is  ok .
Sorry to disturb you again , just for  clarification .
thank  you
Mussa (rep: 48) Feb 16, '23 at 6:01 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login