Use Excel Macro in Access
 

Hi
I wrote an excel macro that works perfectly but only runs for 30,00
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 hav
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(Sp pTo _
, 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(Ra nge("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(Sp pTo _
, 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 Su

--
bharatPosted from http://www.pcreview.co.uk/ newsgroup acces

Use Excel Macro in Access
 

change variable i to long data type..

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


bharat wrote :


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(Sp pTo _
, 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(Ra nge("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(Sp pTo _
, 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