Results 1 to 2 of 2

Thread: Looking to make this macro Faster

  1. #1

    Looking to make this macro Faster

    Sub STEP12()  Dim wb1 As Workbook, ws1 As Worksheet, Lr1 As Long
      Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\1.xls")
      Set ws1 = wb1.Worksheets.Item(1)
      Let Lr1 = ws1.Range("H" & ws1.Rows.Count).End(xlUp).Row
       ws1.Range("J2:J" & Lr1 & "").Value = "=IF(H2>D2,0.40/100*H2,IF(H2<D2,0.40/100*H2,""D is equal to H""))"
       ws1.Range("J2:J" & Lr1 & "").Value = ws1.Range("J2:J" & Lr1 & "").Value
       ws1.Range("K2:K" & Lr1 & "").Value = "=IF(H2>D2,H2-J2,IF(H2<D2,H2+J2,""D is equal to H""))"
       ws1.Range("K2:K" & Lr1 & "").Value = ws1.Range("K2:K" & Lr1 & "").Value
    End Sub

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Oct 2006
    Near Columbia
    Let is redundant: Lose it
    & "" Does nothing: Lose them
    IF(H2>D2 & IF(H2<D2 Used together like that mean H2<>D2: Use a single IF Statement.
    0.40/100 = 0.04%" Are you sure that's what you want? If so. just use 0.0004*H2

    To really speed up that code, use Arrays for D, H, J, & K, then do all processing in memory before "pasting" J & K back into the sheet
    I expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts