Posts by hn.rocket

Re: Copy items w/multiple qty from 1 sheet to individual lines in another sheet

wow guys,

I just fixed it..

just changed this:

c1.EntireRow.selection

to this:

c1.EntireRow.Copy

Re: Copy items w/multiple qty from 1 sheet to individual lines in another sheet

Hi,

I changed this section:

Range("A" & rowcount_no2).Offset(1, 0).EntireRow.select
selection.paste

to this:

Range("A" & rowcount_no2).Offset(1, 0).EntireRow.PasteSpecial xlPasteValues

And the pasting of the first sku-1 works then it stops, and I get an error (error_2 attached) here:

' select the entire row in new sheet and copy it according to qty_count
[B]    c1.EntireRow.Select[/B]
    Selection.Copy

Re: Copy items w/multiple qty from 1 sheet to individual lines in another sheet

Hi,

This was to copy the items in sheet5 x amount of time according to the qty

Quote from Sixth_S.e.n.s.e;654689

Please explain what is the purpose of this loop in your code?

Code

For i = 1 To qty_count

Hi,

I'm trying to copy items from sheet 5 on single lines in sheet 6 based on qty value.

e.g

sheet5:

sku-2 2

sheet6 should have:

sku-2
sku-2

But I'm getting an error at this line(attached):

selection.paste

Thank you for any assistance you guys can provide.

Sub inventory()




Dim i As Integer
Dim rowcount_no, rowcount_no2 As Integer
Dim qty_count As Integer




' Find the last used row, this works even if the data is offset, and there are blanks
' e.g. col_1row_1 = a, col_1row_2 = blank, col_1row_3 = b, col_1 row_4 = blank / col_2 row_4 = c




rowcount_no = Worksheets("sheet5").UsedRange.Rows.Count
MsgBox rowcount_no 'Total Row Count with Data'




' Go through each line and detect the qty of skus




Worksheets("Sheet5").Activate




For Each c1 In Worksheets("sheet5").Range("B1:B" & rowcount_no).Cells




    ' select the qty column and check the qty for each row
    qty_count = c1.value
    MsgBox qty_count
      
    ' select the entire row in new sheet and copy it according to qty_count
    c1.EntireRow.Select
    Selection.Copy
           
    
    ' Select the last empty row being used in sheet6 and paste
    Worksheets("Sheet6").Activate
    rowcount_no2 = Worksheets("sheet6").UsedRange.Rows.Count
    For i = 1 To qty_count
        Range("A" & rowcount_no2).Offset(1, 0).Select
        Selection.Paste
    Next i
    
    
            
    
Next
    
End Sub

Re: Problem with code for Clearing Content in Column Using User Input

Hi jproffer.

Thanks for the reply. That didn't work.. It's quite baffling why the code works if I explicitly put in the value, yet the Variable doesn't work.
Other suggestions would appreciate it.
James.

Quote from jproffer;603958

CountA only counts non-blank cells, so if there are blanks in that column the loop won't go all the way to the bottom. That may not be an issue in this case, but try this and see:

Code

Private Sub CommandButton1_Click()
     
     ' This will delete specific value in column
     
    Dim i As Integer
    Dim ColCount As Integer
    Dim DelValue As Variant
     
    DelValue = InputBox("Value to Delete?")
    ColCount = Range("B" & Rows.Count).End(xlUp).Row
     
     
    For i = 1 To ColCount
         
        If Cells(i, 2).Value = DelValue Then Cells(i, 2).ClearContents
         
    Next i
     
End Sub

Display More

If that don't work, post your workbook and someone might see something else causing your troubles.

Hi,

Problem: The code runs, but does not clear the value entered in the inputbox, not sure what's wrong

This works if I take out the DelValue in this line and insert the value such as

[TABLE="width: 70"]

[TD="width: 70, align: right"]200007649[/TD]

[/TABLE]

If Cells(i, 2).Value = DelValue Then Cells(i, 2).ClearContents

The code runs without errors, but does not clear the contents

Sample Data for col 2

[TABLE="width: 70"]

order_nr

[TD="align: right"]200007649[/TD]

[TD="align: right"]200008649[/TD]

[TD="align: right"]200009649[/TD]

[TD="align: right"]200006649[/TD]

[TD="align: right"]200006649[/TD]

[TD="align: right"]200004649[/TD]

[/TABLE]

Thank You in Advance
James

Private Sub CommandButton1_Click()


' This will delete specific value in column


Dim i As Integer
Dim ColCount As Integer
Dim DelValue As Variant


DelValue = InputBox("Value to Delete?")
ColCount = Application.WorksheetFunction.CountA(Columns(2))




For i = 1 To ColCount


If Cells(i, 2).Value = DelValue Then Cells(i, 2).ClearContents


Next i


End Sub