Hi
I wrote an excel macro that works perfectly but only runs for 30,000 records. I need more than that and I have all my data in MS Access.
How can I convert this macro to Access? For your information, I have attache the entire macro. Any help is highly appreciated. Thank you
Bharat
Dim SIFrom As Double
Dim SITo As Double
Dim spp As String
Dim SppFrom As String
Dim SppTo As String
Dim i As Integer
For i = 5 To 30000
If Range("E" & i).FormulaR1C1 = "" Then
Range("E5").Select
Exit Sub
End If
Sheets("Macro").Select
Range("D" & i).Select
SIFrom = ActiveCell.Value
Range("C" & i).Select
SppFrom = UCase(ActiveCell.Value)
'Unknown Species
Range("E" & i).Select
SppTo = UCase(ActiveCell.Value)
If Range("C" & i) = "" Or Range("D" & i) = "" Then
SIFrom = 60
SppFrom = "RN"
Else
End If
Dim A1 As Double
Dim A2 As Double
Dim AspenTo1 As Double
Dim AspenTo2 As Double
Dim AspenFrom1 As Double
Dim AspenFrom2 As Double
A1 = Range("AC4:CR71").Cells(WorksheetFunction.Match(SppTo _
, Range("AB4:AB71"), 0), WorksheetFunction.Match(SppFrom, Range("AC3:CR3"), 0)).Value
A2 = Range("AC77:CR144").Cells(WorksheetFunction.Match(SppTo _
, Range("AB77:AB144"), 0), WorksheetFunction.Match(SppFrom, Range("AC76:CR76"), 0)).Value
'Coefficients when Conversion through Aspen for not associated species
AspenTo1 = Range("AC4:CR71").Cells(WorksheetFunction.Match(Range("AB44") _
, Range("AB4:AB71"), 0), WorksheetFunction.Match(SppFrom, Range("AC3:CR3"), 0)).Value
AspenTo2 = Range("AC77:CR144").Cells(WorksheetFunction.Match(Range("AB44") _
, Range("AB77:AB144"), 0), WorksheetFunction.Match(SppFrom, Range("AC76:CR76"), 0)).Value
AspenFrom1 = Range("AC4:CR71").Cells(WorksheetFunction.Match(SppTo _
, Range("AB4:AB71"), 0), WorksheetFunction.Match(Range("AB44"), Range("AC3:CR3"), 0)).Value
AspenFrom2 = Range("AC77:CR144").Cells(WorksheetFunction.Match(SppTo _
, Range("AB77:AB144"), 0), WorksheetFunction.Match(Range("AB44"), Range("AC76:CR76"), 0)).Value
If SppFrom = SppTo Then
SITo = SIFrom
Else
If A1 <> 0 And A2 <> 0 Then
SITo = A1 + A2 * SIFrom
Else
If AspenTo1 = 0 Or AspenFrom1 = 0 Then
SITo = SIFrom
Else
SIAspen = AspenTo1 + AspenTo2 * SIFrom
SIThroughAspen = AspenFrom1 + AspenFrom2 * SIAspen
SITo = SIThroughAspen
End If
End If
End If
Sheets("Macro").Select
Range("F" & i).Select
ActiveCell.Value = SITo
Next i
End Sub
I wrote an excel macro that works perfectly but only runs for 30,000 records. I need more than that and I have all my data in MS Access.
How can I convert this macro to Access? For your information, I have attache the entire macro. Any help is highly appreciated. Thank you
Bharat
Dim SIFrom As Double
Dim SITo As Double
Dim spp As String
Dim SppFrom As String
Dim SppTo As String
Dim i As Integer
For i = 5 To 30000
If Range("E" & i).FormulaR1C1 = "" Then
Range("E5").Select
Exit Sub
End If
Sheets("Macro").Select
Range("D" & i).Select
SIFrom = ActiveCell.Value
Range("C" & i).Select
SppFrom = UCase(ActiveCell.Value)
'Unknown Species
Range("E" & i).Select
SppTo = UCase(ActiveCell.Value)
If Range("C" & i) = "" Or Range("D" & i) = "" Then
SIFrom = 60
SppFrom = "RN"
Else
End If
Dim A1 As Double
Dim A2 As Double
Dim AspenTo1 As Double
Dim AspenTo2 As Double
Dim AspenFrom1 As Double
Dim AspenFrom2 As Double
A1 = Range("AC4:CR71").Cells(WorksheetFunction.Match(SppTo _
, Range("AB4:AB71"), 0), WorksheetFunction.Match(SppFrom, Range("AC3:CR3"), 0)).Value
A2 = Range("AC77:CR144").Cells(WorksheetFunction.Match(SppTo _
, Range("AB77:AB144"), 0), WorksheetFunction.Match(SppFrom, Range("AC76:CR76"), 0)).Value
'Coefficients when Conversion through Aspen for not associated species
AspenTo1 = Range("AC4:CR71").Cells(WorksheetFunction.Match(Range("AB44") _
, Range("AB4:AB71"), 0), WorksheetFunction.Match(SppFrom, Range("AC3:CR3"), 0)).Value
AspenTo2 = Range("AC77:CR144").Cells(WorksheetFunction.Match(Range("AB44") _
, Range("AB77:AB144"), 0), WorksheetFunction.Match(SppFrom, Range("AC76:CR76"), 0)).Value
AspenFrom1 = Range("AC4:CR71").Cells(WorksheetFunction.Match(SppTo _
, Range("AB4:AB71"), 0), WorksheetFunction.Match(Range("AB44"), Range("AC3:CR3"), 0)).Value
AspenFrom2 = Range("AC77:CR144").Cells(WorksheetFunction.Match(SppTo _
, Range("AB77:AB144"), 0), WorksheetFunction.Match(Range("AB44"), Range("AC76:CR76"), 0)).Value
If SppFrom = SppTo Then
SITo = SIFrom
Else
If A1 <> 0 And A2 <> 0 Then
SITo = A1 + A2 * SIFrom
Else
If AspenTo1 = 0 Or AspenFrom1 = 0 Then
SITo = SIFrom
Else
SIAspen = AspenTo1 + AspenTo2 * SIFrom
SIThroughAspen = AspenFrom1 + AspenFrom2 * SIAspen
SITo = SIThroughAspen
End If
End If
End If
Sheets("Macro").Select
Range("F" & i).Select
ActiveCell.Value = SITo
Next i
End Sub