Modified code copy data from userform to the empty last columns

[Solved]
Report
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021
-
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021
-
hello
this is the first post in this forum and I hope finding solution here
I got this code from the internet and works well but I need some adjusting so the code copy data from userform (textbox1,2,3) to sheet column b,c,d after select from combobox based on column a the column a contains many items so the copy occurs for specific item in column a and insert row before the total
so what I want add in userform textbox4,5 so when I fill data for specific item then textbox1,2,3 always copies in column b,c,d but the texbox4,5 should copy to two empty last column because every month I have to insert two columns so the empty columns changes every month
as for to insert the empty row before total the original code does that but it has to be empty row before total row I don't want any empty row before the total it should insert empty row and fill it when I fill data from userform

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim fnd As Range, SH As Worksheet, DEMPTY As Long, LR As Long
Set SH = Sheets("SH1")
LR = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set fnd = SH.Range("A:A").Find(ComboBox1.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not fnd Is Nothing Then
With SH
DEMPTY = .Range("B" & fnd.Row - 1 & ":B" & LR).Find("", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
Range("B" & DEMPTY).Resize(, 4).Copy
Range("B" & DEMPTY + 1).Insert shift:=xlDown
.Range("B" & DEMPTY).Resize(, 3).Value = Array(TextBox1.Value, TextBox2.Value, TextBox3.Value)
Application.CutCopyMode = False
End With
End If
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
ComboBox1 = ""
Application.ScreenUpdating = True
End Sub

thanks

6 replies

Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi LeapOut,

I find your query hard to understand.

You want to find the combobox value in column A. Then use that row to enter data from textbox 1, 2 and 3 into column B, C and D. So i guess those cells are empty, right? Then on the same row, you want to find the last used column to place the values from textbox 4 and into those column. So in your sheet you have a value in column A, then B, C and D are empty and then for example E, F and G contain values.

And then you want to insert that row of data to the row before the totals row (last row)?

Sample data before code:


Sample data after code:


Did I understand you correctly?

Best regards,
Trowa
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

Hi Trowa
sorry about the data is poor as you see in above three columns 1,2 theses are months I have to insert every month so if the brand is not existed in column b when first time add a new brand like "AA" then textbox1,2,3= col b,c,d and the textbox4,5= the next columns are empty in e,f the column g it contains the formula subtract the values between e,f and if I return fill again the brand is existed in a new month 2 then move values to the next empty columns h,i and the column g contains formula and so on every month I have to insert three columns the first two columns should fill by textbox4,5
with considering if I fill a new brand in column b then insert empty row and fill it by texbox1,2,3 and if the brand is existed as in column b,c,d then should ignore it it must not add repeated brands
I know you see this way is very complicated but if you have any idea to make the matter is easy I don't mind
and I hope this help to understand what I want

Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi Leapout,

Here it is, the latest version of the code:
Private Sub CommandButton1_Click()
Dim iFind, bFind As Range
Dim fRow, lRow As Long, rDif, qMB As Integer

Set iFind = Columns("A").Find(ComboBox1.Value)
If iFind Is Nothing Then
    qMB = MsgBox("Would you like to add " & ComboBox1.Value & " as a new item?", vbYesNo)
    If qMB = vbNo Then Exit Sub
    fRow = Range("A" & Rows.Count).End(xlUp).Row
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    rDif = lRow - fRow
    Rows(fRow & ":" & lRow).Copy
    Range("A" & lRow + 1).PasteSpecial
    Selection.SpecialCells(xlCellTypeConstants).ClearContents
    Range("A" & lRow + 1).Value = ComboBox1.Value
    Range("B" & lRow + 1 + rDif).Value = "TOTAL"
    If rDif > 2 Then
        For x = rDif To 2 Step -1
            Rows(Range("B" & Rows.Count).End(xlUp).Row - 1).Delete
        Next x
    End If
    Cells(lRow + 1, "B").Value = TextBox1.Value
    Cells(lRow + 1, "C").Value = TextBox2.Value
    Cells(lRow + 1, "D").Value = TextBox3.Value
    fRow = lRow + 1
    lRow = fRow + 1
Else
    fRow = iFind.Row
    If Range("B" & fRow).Value = vbNullString Then
        lRow = fRow
    Else
        lRow = iFind.End(xlDown).Row - 2
    End If
    If lRow = 1048574 Then lRow = Range("B" & Rows.Count).End(xlUp).Row - 1
End If

Set bFind = Range(Cells(fRow, "B"), Cells(lRow, "B")).Find(TextBox1.Value)
If bFind Is Nothing Then GoTo nBTO
firstAddress = bFind.Address
Do
    If Range("C" & bFind.Row).Value <> TextBox2.Value Or Range("D" & bFind.Row).Value <> TextBox3.Value Then
        Set bFind = Range(Cells(fRow, "B"), Cells(lRow, "B")).FindNext(bFind)
    Else
        GoTo sBTO
    End If
Loop While bFind.Address <> firstAddress

nBTO:
If Range("B" & lRow).Value <> vbNullString Then
    Rows(lRow).Copy
    Rows(lRow).Insert shift:=xlUp
    lRow = lRow + 1
    Rows(lRow).SpecialCells(xlCellTypeConstants).ClearContents
End If
Cells(lRow, "B").Value = TextBox1.Value
Cells(lRow, "C").Value = TextBox2.Value
Cells(lRow, "D").Value = TextBox3.Value
Cells(lRow, Columns.Count).End(xlToLeft).Offset(0, -2).Value = TextBox4.Value
Cells(lRow, Columns.Count).End(xlToLeft).Offset(0, -1).Value = TextBox5.Value

Unload UserForm1

Exit Sub

sBTO:
Cells(bFind.Row, Columns.Count).End(xlToLeft).Offset(0, -2).Value = TextBox4.Value
Cells(bFind.Row, Columns.Count).End(xlToLeft).Offset(0, -1).Value = TextBox5.Value

Unload UserForm1

End Sub


I left out the Initialize part, as that is unchanged.

Best regards,
Trowa
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

Hi,Trowa
you're did a huge great and every thing works excellently but I have some details I would ask for them if you don't mind actually I try learning from you and the others how the macros works , I note when I fill data in userform without select combobox it also copy to the first item so to I overcome with this problem I added this line from the beginning
of code
If ComboBox1.ListIndex = -1 Then MsgBox " you  have  to select  the  item from combobox": Exit Sub

actually it works , first I would check this line is it logical if it's yes then I would explain me the value -1 and properties what means listindex because I got this idea from other website without I know what means .
the second I would ask you for when you use loop through rows or columns , does loop through the filled of data for rows & columns or also empty cells if they're both ,then if it's possible just loop through the filled data for cells whether rows or columns to doesn't make slow work of the code
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482 >
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

Hi Leapout,

Good to hear you're happy with the code.

ListIndex start counting from 0. When you have 3 items, ListIndex will say 2: List item 0, 1 and 2. The -1 means the value before 0 and since there is no value before 0, it means no value. Personally I would use:
ComboBox1.Value = vbNullstring
If your ComboBox has an empty value in it, then this will work as well instead of ListIndex method.

For your second question, you could use .SpecialCells(xlCellTypeConstants) to just look at the values of a range and skipping the blanks. I did a test on column A for 10000 rows using the specialcells method and the method where I check if the cell is empty. The code had to place a value in the cell next to it, if the cell was not empty. Both methods took the same amount of time.

Best regards,
Trowa
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021
>
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021

thanks for your explanation and of course for your great code to complete my project
Best regards,
Leapout
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi Leapout,

So you search column A for Item (Combobox1).
- Not found, then add another Item at the bottom of your table.
- When found, then search column B for brand (Textbox1)
- - Not found, then insert a row above the Total row for that Item.
- - When found, then use that row to enter Import (Textbox4) and Export (Textbox5) values.

How do we know for which month to put the Import and Export values? Is it always the last month? This is confusing as you said to enter the data in the first empty columns, but for Brand BB that would be columns E and F, but you placed them in columns H and I.

Also, can an Item be 'not found' or is your Item list complete?

Do you have a limit to the amount of months? Like this sheet has 12 months to complete the year and then the next year you move to the next sheet or different file? Or do you just keep adding month?

It is getting pretty complex. Keep in mind that creating a solution, once it is clear what you want, will take some time.

Best regards,
Trowa
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

about ask this
which month to put the Import and Export values? Is it always the last month?
I can said yes every month I have to insert three columns so when fill data in all textboxes whether I've found the brand is existed or not should put the values in columns contain import and export in last month
about you ask this
but for Brand BB that would be columns E and F, but you placed them in columns H and I.
you're right in one case if I have only month 1 should be but I insert a new month 2 then change the columns and I ignore the past month I don't returns again should fill the values always the last month without look the past month because it's finished and closed , briefly about the the data in columns A,B,C it always fills when I write a new data are not existed and if existed don't repeat writing more again just add the values in last month
as to ask this Do you have a limit to the amount of months?
just I add month without specify the year start 1 up to 12 months and start again 1 up to 12
about it takes more time to find the solution I don't mind take your time and if you want more explanation please inform me
I appreciate for your interesting to solve my problem
thanks again
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482 >
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

Hi Leapout,

Alright, great. You just missed a question. Can an Item be 'Not found'? I mean in your sample data you have 3 items (CD-1000, CD-1001 and CD-1002), could you be searching for item CD-1003? Or do you only search for items that are on your list?

And just to make sure we are not missing something, you have 6 fields on your userform: 1 combobox and 6 textboxes, right?

Best regards,
Trowa
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021
>
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021

about this
have 3 items (CD-1000, CD-1001 and CD-1002), could you be searching for item CD-1003
yes I have many items CD- 1004,CD-1005 .. and so on I will add many items but above the picture just to understand what I want
as for this
And just to make sure we are not missing something, you have 6 fields on your userform: 1 combobox and 6 textboxes, right?
actually I have one combobox to select item based on col A and textbox1,2,3 are relating "brand, type and origin"
and textbox4,5 are relating values import and export about the col NET i ignore it because contains formula after add the values in import and export it will account the values
thanks again
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482 >
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

Hi Leapout,

Yeah I made a typo. I meant 1 combobox and 5 textboxes, but you confirmed that.

The item question is still not clear to me though. I know you posted just some sample data and that your actual file has many more items. My question is: Do you want the userform to add items OR do you add items manually?

To clearify, if you add items manually, then the userform will always find the item. Which would be easiest. Otherwise some added coding is required to identify if an item is there or not and then to add an item if it is not found.

Best regards,
Trowa
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

Hi Trowa ,
about the items , indeed I added manually , but I wish do that by userform you can add another textbox to add it but if you add a new textbox for a new item it should add it after row contains TOTAL I prefer do that by userform if you see this is not right way , you can do manually , I 'm open for all suggestion
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi Leapout,

Just so we are on the same page, could you give the following code a try. You can then point out the things you want to add/change.

The code requires there is always a free/empty month available with a formula in the last column used (which is the NET column).
The code also requires that the Item is always found.

Here is the code:
Private Sub CommandButton1_Click()
Dim iFind, bFind As Range
Dim fRow, lRow As Long

Set iFind = Columns("A").Find(ComboBox1.Value)
fRow = iFind.Row
lRow = iFind.End(xlDown).Row - 2

Set bFind = Range(Cells(fRow, "B"), Cells(lRow, "B")).Find(TextBox1.Value)
If bFind Is Nothing Then
    Rows(lRow).Copy
    Rows(lRow).Insert shift:=xlUp
    Rows(lRow + 1).SpecialCells(xlCellTypeConstants).ClearContents
    Cells(lRow + 1, "B").Value = TextBox1.Value
    Cells(lRow + 1, "C").Value = TextBox2.Value
    Cells(lRow + 1, "D").Value = TextBox3.Value
    Cells(lRow + 1, Columns.Count).End(xlToLeft).Offset(0, -2).Value = TextBox4.Value
    Cells(lRow + 1, Columns.Count).End(xlToLeft).Offset(0, -1).Value = TextBox5.Value
Else
    Cells(bFind.Row, Columns.Count).End(xlToLeft).Offset(0, -2).Value = TextBox4.Value
    Cells(bFind.Row, Columns.Count).End(xlToLeft).Offset(0, -1).Value = TextBox5.Value
End If

Unload UserForm1

End Sub

Private Sub UserForm_Initialize()
For Each cell In Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value <> vbNullString Then ComboBox1.AddItem cell.Value
Next cell
End Sub


I'll now start on adding the option to add Items which are not found. I'll add in a message to ask for confirmation in case of a typo.

Best regards,
Trowa

Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

it's a great , but there is a problem when I add the values to specific brands it should to see column B,C,D together because may be the column B,C repeats but the different is in column C so I tried it
for instance
brand type origin
AA ASD1 MM
AA ASD1 NN
as you see when I try add the values to the first it ignores and move to the next because it looks only to COL B "BRAND"
and what about add a new items and how add a new BRAND ,TYPE , ORIGIN ?
I hope to I reached my idea
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
Hi Leapout,

Ok, great feedback.

I think I added all functionality's, so give the code below a thorough test.
  • added the option to add a new item, confirmation is asked in case of a typo.
  • looking for the brand also checks type and origin.


Here is the updated code:
Private Sub CommandButton1_Click()
Dim iFind, bFind As Range
Dim fRow, lRow As Long, rDif, qMB As Integer

Set iFind = Columns("A").Find(ComboBox1.Value)
If iFind Is Nothing Then
    qMB = MsgBox("Would you like to add " & ComboBox1.Value & " as a new item?", vbYesNo)
    If qMB = vbNo Then Exit Sub
    fRow = Range("A" & Rows.Count).End(xlUp).Row
    lRow = Range("B" & Rows.Count).End(xlUp).Row
    rDif = lRow - fRow
    Rows(fRow & ":" & lRow).Copy
    Range("A" & lRow + 1).PasteSpecial
    Selection.SpecialCells(xlCellTypeConstants).ClearContents
    Range("A" & lRow + 1).Value = ComboBox1.Value
    Range("B" & lRow + 1 + rDif).Value = "TOTAL"
    If rDif > 2 Then
        For x = rDif To 2 Step -1
            Rows(Range("B" & Rows.Count).End(xlUp).Row - 1).Delete
        Next x
    End If
    Cells(lRow + 1, "B").Value = TextBox1.Value
    Cells(lRow + 1, "C").Value = TextBox2.Value
    Cells(lRow + 1, "D").Value = TextBox3.Value
    fRow = lRow + 1
    lRow = fRow + 1
Else
    fRow = iFind.Row
    If Range("B" & fRow).Value = vbNullString Then
        lRow = fRow
    Else
        lRow = iFind.End(xlDown).Row - 2
    End If
    If lRow = 1048574 Then lRow = Range("B" & Rows.Count).End(xlUp).Row - 1
End If

Set bFind = Range(Cells(fRow, "B"), Cells(lRow, "B")).Find(TextBox1.Value)
If bFind Is Nothing Then GoTo nBTO
If Range("C" & bFind.Row).Value = TextBox2.Value And Range("D" & bFind.Row).Value = TextBox3.Value Then
    Cells(bFind.Row, Columns.Count).End(xlToLeft).Offset(0, -2).Value = TextBox4.Value
    Cells(bFind.Row, Columns.Count).End(xlToLeft).Offset(0, -1).Value = TextBox5.Value
Else
nBTO:
    If Range("B" & lRow).Value <> vbNullString Then
        Rows(lRow).Copy
        Rows(lRow).Insert shift:=xlUp
        lRow = lRow + 1
        Rows(lRow).SpecialCells(xlCellTypeConstants).ClearContents
    End If
    Cells(lRow, "B").Value = TextBox1.Value
    Cells(lRow, "C").Value = TextBox2.Value
    Cells(lRow, "D").Value = TextBox3.Value
    Cells(lRow, Columns.Count).End(xlToLeft).Offset(0, -2).Value = TextBox4.Value
    Cells(lRow, Columns.Count).End(xlToLeft).Offset(0, -1).Value = TextBox5.Value
End If

Unload UserForm1

End Sub

Private Sub UserForm_Initialize()
For Each cell In Range("A3:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value <> vbNullString Then ComboBox1.AddItem cell.Value
Next cell
End Sub


Best regards,
Trowa
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

Hi Trowa,
wow ! that's extremely great well done ! but I have a problem I no know if the problem is from me or code actually as in my picture in post 4# the item CD-1000 from row 3 is the problem if the brand is existed it should just add the values without insert row and repeat again the brand and type and origin but the code it doesn't do that otherwise the rests items works correctly as what I want please guide me what I make mistake
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482 >
Posts
11
Registration date
Monday March 1, 2021
Status
Member
Last seen
August 11, 2021

Hi Leapout,

Sorry for the late reply, had to deal with some corona stuff.

Good find. I notice that when there are more of the same Brand within an Item, the code checks only one Brand to see if the Type and Origin also match. If the Type and Origin don't match, the code doesn't look any further and just adds another row, while there could be a match on another row within the rows of an Item. I will need to add another loop to check all Brand rows within an Item.

I will get back to you.

Best regards,
Trowa