Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Call an Access macro from an Excel macro | Excel Discussion (Misc queries) | |||
Run Access Macro from within Excel | Excel Programming | |||
Trigger a Macro in MS Access from an Excel Macro? | Excel Programming | |||
run a macro in excel from an access vba | Excel Programming | |||
Launch Macro in Access via Macro running in Excel??? | Excel Programming |