VBA using FormulaR1C1 and multiply value from other sheet then if zero delete row

[Jason Gauthier]

Hello,

I am new to VBA but trying to learn. I am trying to have a sheet that has 2 Macros, the 2nd Macro will send the data to an IE webpage, this part I have done. I Recorded my Macro to copy values from one sheet to another and do some multiplication, this is where I get stuck, the Webpage cannot be sent zeros, and all the research I have done on Macros to remove zeros does not seem to work as there is a formula in the cell not a zero. Looking for coding to work after each multiply that if it equaled zero it would delete the row. Any help would be very appreciated.

Sub Copy()
'
' Copy Macro
'


Worksheets("Aldi Sheet").Rows(6).Delete Shift:=xlShiftUp
Range("A1").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[5]C[1]"
Range("B1").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[5]C[-1]"
Range("B2").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[1]C[1]"
Range("B3").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!RC[2]"
Range("B4").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-1]C[3]"
Range("B5").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-2]C[4]"
Range("B6").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-3]C[5]"
Range("B7").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-4]C[6]"
Range("B8").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-5]C[7]"
Range("B9").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-6]C[8]"
Range("B10").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-7]C[9]"
Range("B11").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-8]C[10]"
Range("B12").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-9]C[11]"
Range("C2").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[4]C*80"
Range("C3").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[3]C[1]*80"
Range("C4").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[2]C[2]*4"
Range("C5").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[1]C[3]*80"
Range("C6").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!RC[4]*80"
Range("C7").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-1]C[5]*6"
Range("C8").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-2]C[6]*6"
Range("C9").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-3]C[7]*6"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-4]C[8]*6"
Range("C11").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-5]C[9]*6"
Range("C12").Select
ActiveCell.FormulaR1C1 = "='Aldi Sheet'!R[-6]C[10]*6"
Range("C13").Select



End Sub

 

[baitmaster]

Welcome to the board

For a start you can get rid of all that selecting which is absolutely unnecessary, and a byproduct of the macro recorder tracking every action you do. Refer to your object (in this case each range) and then apply the method (action) to it, for example

[COLOR=#333333]Range("A1").[/COLOR][COLOR=#333333]FormulaR1C1 = "='Aldi Sheet'!R[5]C[1]"[/COLOR]
[COLOR=#333333]Range("B1").[/COLOR][COLOR=#333333]FormulaR1C1 = "='Aldi Sheet'!R[5]C[-1]"[/COLOR]

As for the other point, there's a heap of ways this could be done. Probably easiest if you show me your current code for writing to the webpage, and I'll show you how to simply ignore zeroes using an IF statement. Maybe something like

' declare a range object called cl
dim cl as range

' loop through a range of cells and decide what to do with each one
for each cl in range("my range here")

    ' ignore zero values
    if cl.value = 0 then goto nextCl
    
    ' perform your required cell action here:



nextCl: ' named location in your code

next cl ' end of loop, actions repeat until no more cells to process

 

[Jason Gauthier]

Thank you baitmaster, I will remove all the Range select's from code.

The code for sending to Webpage is below.
Thank you for your help on this, I was thinking somewhere along the same as you to ignore the zeros, where I get stuck is the code is sending 2 values Item number which is always populated then the Quantity, the Quantity cannot be zero when sent to the web or application also can not have just the item number sent this is where I was hoping for a code IF the calculation = 0 it would delete the row.

Sub ie_stuff()
'Jason fix for Windows 10 & IE11 added Module 2, delcared time by dvMilliseconds
    Range("B2").Select
    AppActivate ("eRMS PRODUCTION Windows - Internet Explorer")
    Do While ActiveCell.Value <> ""
        item_num = ActiveCell.Value
        item_qty = ActiveCell.Offset(0, 1).Value
        Sleep 600
        SendKeys (item_num)
        Sleep 600
        SendKeys ("{Tab}")
        Sleep 600
        SendKeys (item_qty)
        Sleep 600
        SendKeys ("{ENTER}")
        ActiveCell.Offset(1, 0).Range("A1").Select
    Loop


    SendKeys "{NUMLOCK}", True
End Sub

 

[baitmaster]

Option Explicit ' forces you to declare all variables - makes your code robust plus you get meaningful error messages

Sub ie_stuff()


'Jason fix for Windows 10 & IE11 added Module 2, delcared time by dvMilliseconds
    
' create range for processing
Dim rngStart As Range: Set rngStart = Range("B2")


Dim rngToProcess As Range
If rngStart.Offset(1, 0) = "" Then
    Set rngToProcess = rngStart
Else
    Set rngToProcess = Range(rngStart, rngStart.End(xlDown))
End If


' select IE
AppActivate ("eRMS PRODUCTION Windows - Internet Explorer")


' loop through all cells and take appropriate action
Dim cl As Range, item_num, item_qty ' num, qty should be declared as e.g. integer but without seeing the data it's not possible to discern correct data type, so are left as variants
For Each cl In rngToProcess


    item_num = cl.Value
    item_qty = cl.Offset(0, 1).Value
        
    ' only process if there is a quantity. There should be an item_num as we've created a range of non-blanks
    If item_qty <> 0 Then
        Sleep 600
        SendKeys (item_num)
        Sleep 600
        SendKeys ("{Tab}")
        Sleep 600
        SendKeys (item_qty)
        Sleep 600
        SendKeys ("{ENTER}")
    End If
    
Next cl


SendKeys "{NUMLOCK}", True


End Sub

 

[Jason Gauthier]

This is perfect! Thank you very much Baitmaster! I will need to continue to learn VBA thanks again for the help.