WorksheetFunction

[kuvailondra]

Hi All,

I have come across a code which am having hard time with. I need the userform information to be entered into another sheet, which is not working. I need the Macro to update the table with new/edit previous information.

Dim id As Integer, i As Integer, j As Integer, flag As Boolean

Sub GetData()

If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
id = UserForm1.TextBox1.Value

Do While Cells(i + 1, 1).Value <> ""

If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 3
UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
Next j
End If

i = i + 1

Loop

If flag = False Then
For j = 2 To 3
UserForm1.Controls("TextBox" & j).Value = ""
Next j
End If

Else
ClearForm
End If

End Sub

Sub ClearForm()

For j = 1 To 3
UserForm1.Controls("TextBox" & j).Value = ""
Next j

End Sub

Sub EditAdd()

Dim emptyRow As Long

If UserForm1.TextBox1.Value <> "" Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
emptyRow = WorksheetFunction.CountA(Range(Worksheets("Uniform Table").Range("A1:D82"))) + 1

Do While Cells(i + 1, 1).Value <> ""

If Cells(i + 1, 1).Value = id Then
flag = True
For j = 2 To 3
Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If

i = i + 1

Loop

If flag = False Then
For j = 1 To 3
Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
Next j
End If

End If

End Sub

 

[alansidman]

Instead of trying to amend someone else's code that doesn't work, suggest you tell us what issues you are having and let us provide you with a solution that is designed specifically for you.

 

[kuvailondra]

I am very new to macros. i am trying to create a file where user can add/edit current table with new data. 3 different commands, Stock count (user can edit old information), Stock in (add's to current numbers) and Stock out (Minus current stock). i have the table in new sheet. i need the command buttons to start Forum where user has to enter and these will then change the data on a table in a new sheet.

 

[alansidman]

Show us some sample data. Macros are specific to your data. Explain your situation using the sample data. Your explanation is far to vague and we cannot see your data layouts. Help us to help you with being specific in your requests. More information is always better than less.

 

[DanteAmor]

Hi @kuvailondra, welcome to the forum.

It is a good idea to create, update or delete data from a userform.
But for that several codes are required. If you like, we help you with the first option: Create records

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[kuvailondra]

Hi @kuvailondra, welcome to the forum.

It is a good idea to create, update or delete data from a userform.
But for that several codes are required. If you like, we help you with the first option: Create records

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

Hi @DanteAmor

Thank you

So to explain the situation;

1, the main page layout had 4 options with command button which user can use depending on the task

https://imgur.com/SI4hMvO

2, once clicked on a command button, forum opens up

https://imgur.com/VOeQhn9

3, Forum is made out of 3 options. these 3 options is used for data entry. Any data added will transfer information to Uniform Table

https://imgur.com/14Oxld2

4, Uniform table is where all the data from user form will be populated. names of the merchandise is populated using Vlookup from another sheet.

https://imgur.com/SLX9XJn

5, New stock name and ID number

https://imgur.com/n9ijuUF

I can share the file if this will make it easier. if i can be shown where and how to upload it will be useful.

thank you all for your help

 

[DanteAmor]

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[kuvailondra]

You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Hi @DanteAmor

https://www.dropbox.com/s/yzw6apqpooo9yvw/Uniform Tracker v2.xlsm?dl=0

thanks for the help and support

 

[DanteAmor]

Which of all userform do you want to start?

And you explain to me what data you capture and where you want to put each data.

 

[kuvailondra]

Which of all userform do you want to start?

And you explain to me what data you capture and where you want to put each data.

I believe if I start with first option "stock count", where user can amend current table with new count would be good start. So the way am hoping you can help me with is, stock count will use new data entered to overide current "stock table" with new information. If new item is added using the form then it should use +1 row and enter new data in available space.

From the form, the data that will be added to the table is, asset number, quantity and ID.

These information to be added to the table under the relevant columns.

Thank you