Type Mismatch Error

[omagoodness]

Hi everyone
I have code to save user form data to a table and to also add some of the data to another table on the same sheet

The code I am using is exactly the same for both tables with the change in cell and range reference , of course.

In the first instance saving to first table, the code to insert a new table row is as follows:

Please Login or Register  to view this content.

This all works without a hitch

In the next section to save the same date to another table, I have, again in part,

Please Login or Register  to view this content.

The funny part is that sometime it works fine and other times (more often than not, it returns the error.

I looked at both parts of the statement in the Watch Window and it tells me that second part (=Range("Q8").value is a variant. I don't know how to fix that. Like I said, the table cells are formatted as a number without decimals.

Its obvious that I'm missing something but I don't know what it is.

[mehmetcik]

Hi.

My Specialist Subject is Userforms. However I do not understand what you are trying to do.

It looks Messy and slow. But I exist to learn.


Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[omagoodness]

Thank you so much for getting back to me. I will forward a file but I will need to remove sensitive data first. I may not be able to sent it this evening but Ill try. I appreciate your willingness to help.
I agree that it looks messy. I am relatively new to VBA and still have a LOT to learn. I am sure there is an easier way to do what it is I am trying to do.

[omagoodness]

The frmBar is intended to enter product information into Sheets("BarInventory").ListObject("tblBarInventory").
I also want some of these data fields to be entered into the tblBarCount on the same sheet. The 2nd table is used to count and calculate on hand inventory.
On the Sheet("Lists") there is a list called ALCOHOLS. This list is used to populate a combobox on frmSales so I also want the name only to go to that list.
I know that I should be able to draw that information form the tblBarInventory but the Daily Sales form and code I got from a sample workbook
and although I have managed to modify some of the code to meet my needs, I am not experienced and afraid to change too much. Instead I opted to work around so hence a messy, clumsy file.

I am keen to learn and will accept any help you are willing to offer.

[mehmetcik]

I am having to make lots of changes to get this workbook to open.

eg: Date is not a VBA function
The Administration sheet does not exist.

I will look at this once I can open it without errors.

[omagoodness]

I.m so sorry. I thought I had removed all that. Here is a cleaner sample that will show you what you need. Again, thank you so much for your help.

[omagoodness]

I actually think I discovered mt my code was generating an error. I checked the formatting of the sheet column but neglected to check the formatting of the actual table column was was set to "General". I changed it to "Number" with 0 decimals and it seems to work properly (so far). However, if you have time and have any suggestions on how to streamline this process, I would be most grateful.

[mehmetcik]

Ok. Lets take it slowly.

Please give instructions for
the process of opening the userform that you are interested in. There are several in the workbook.

the process for loading data in that userform and where it comes from.

the process for updating your data.

[omagoodness]

This is a small restaurant application I am attempting to build for my son and has many components that are working well. This is a new one that he has asked for.
IN addition to saving the bar inventory to a table, he also requests an inventory count sheet that his bar manager can use as an order sheet and to manage inventory without having access to the actual table. I have set scroll areas so that when the user clicks the appropriate button on the dashboard, he only sees the tblBarCount with the navigation options.
The other thing he asked for was a way to look at daily sales instantly. The DailySales form allows him to do that. It looks like a simple form but it allows the user to use the same comboboxes for several entries that display individually in the textbox below. As I indicated, I got this from a sample workbook and was afraid to alter the source of the combo box data. That is why I also need the name of the item to also go to the Alcohols table on the List sheet.

the process of opening the userform that you are interested in. There are several in the workbook.

There is a dashboard page with user options. In this case there is a shape assigned the macro AddBar to open frmBar

the process for loading data in that userform and where it comes from.

The data in the listbox (listBar) comes from the tblBarInventory on the Sheet names BarInventory. The code for populating the list box is a frmBar initialize event

the process for updating your data.

The user enters the new product in the form. The button >> saves the record and prepares a blank form for the next record. The "Done" button is used when the user is finished entering records or only had one item to enter. The Done command saves the record, displays a saved message and then closes the form.

The information saves to the tblBarInventory without a problem.