1

Can anyone help me create a vba function to execute the following?

In Sheet1 I have 3 columns A(product) and B(price) which are populated with 3000 items and 3rd column C(quantity) which is not populated now.

I want to add quantities to certain products which will be done by finding the product and enter the amount in the corresponding cell C.

Then in Sheet2 or even specified different workbook to have added only all the products and prices and quantity that have quantity introduced.

For example, I have Sheet1:

sheet1

I want to have sheet2 or a specified sheet/workbook automatically populated with the products and their matching prices and quantity that have quantities on like this:

enter image description here

I have managed to create an excel function but the products list is changing weekly and every time I have to copy/paste the formula and adapt the source file and is a complete hassle.

The formula I have made is here:

=IFERROR(INDEX('Products'!$A$5:$C$2655,SMALL(IF((0<'Products'!$C$5:$C$2655),MATCH(ROW('Products'!$B$5:$B$2655),ROW('Products'!$B$5:$B$2655))),ROW(A11)),COLUMN(A11))," ")
  • 1
    The first answer you'll get here is, "This isn't a script writing service". You'll have to provide evidence that you've learned VB code writing skills and then ask for help on errors in your project. Start by recording a Macro, then manipulating that macro manually if you're not sure where to start. Creating Macros, via record, is a good way to learn syntax. – ejbytes Jun 5 '16 at 21:59
  • i am wiling to learn and achieve more knowledge in excel and if that will involve learning VB code okay but the last sentence of your comment is pure extraterrestrial to me. do i have to download a specific software? – dan Jun 5 '16 at 22:09
  • 1
    You don't need specific software for that, just google for "record macro in excel" and will get huge amount of information. – Máté Juhász Jun 6 '16 at 8:16
  • You'd want to reveal the "Developer" tab if it isn't already. Depending on which version of Excel (2007, 2010, 2016) just search "Developer tab Excel 'year'". From there you'll find a "Record Macro" in this tab. This is recording what your actions are in excel, but with no regards to time. That's how I learned VB, in small steps. VB becomes very useful in creating verbose Excel projects. There is also "Use Relative References", which is relative to the cell that is "active" or "selected". Play with it, get to know what things do and what they mean. – ejbytes Jun 7 '16 at 2:19
  • View "view macros" or Alt+F11 to view code. There are two places Excel records VB code, Sheets and Modules. While getting to know VB you'll become more familiar with terms and syntax. Macros is the key to "learning by observation and outcome", or trial an error. As a programmer I can now go into Excel and program the heck out of it, but still learning "syntax". Syntax and programming are both different, but necessary tools and go hand in hand for different programming environments in creating any programming project. – ejbytes Jun 7 '16 at 2:23
1

This VBA code will work:

Public Sub quantityProducts()
    '**********************
    ' variables
    sourceSheet = "Sheet1" 'name of the source sheet
    destSheet = "Sheet2" 'name of the destination sheet
    titleRow = 1 ' Number of Title Row
    firstRowSource = 2 ' First row of source data
    firstRowDest = 2 'First row of data in destination sheet
    copyTitleRow = True 'Should be title row be copied? True / False
    columnToCheck = 3 'Column that defines if the row must be copied
    '**********************
    Dim wkb As Workbook
    Dim wks, wks1 As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Worksheets(sourceSheet)
    Set wks1 = wkb.Worksheets(destSheet)
    wks1.Rows.Clear ' Clear the contents of destination sheet
    If copyTitleRow = True Then 'If title row must be copied
        wks.Rows(titleRow).Copy Destination:=wks1.Rows(titleRow)
    End If
    totalrows = wks.Cells(Rows.Count, 1).End(xlUp).Row ' total rows in source
    destRow = firstRowDest
    For i = firstRowSource To totalrows ' iterate through rows
        If wks.Cells(i, columnToCheck) <> "" Then ' If cell in column to check isn't empty
            wks.Rows(i).Copy Destination:=wks1.Rows(destRow) ' Copy from source to destination
            destRow = destRow + 1 ' Increase value of destination row
        End If
    Next i
    a = MsgBox("Finished. Copied " & destRow - firstRowDest & " rows", vbOKOnly)
End Sub

Open VBA/Macros with ALT+F11, under ThisWorkbook insert a new module and paste the code on the right side.

Check that the values assigned to the variables match with your sheets and execute it clicking the green triangle.

I put comments on the code to let you understand how it works.

You can run it also step by step by clicking on the first line and then go though each step pressing F8.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.