Converting this Excel VBA code to Access VBA. A really challenging task

[satswid]

Hi guys,
Earlier I had a excel file with many columns.
I wrote a code in Excel VBA to fill up the last two columns according to the data in the other columns of the row.
It worked fine.
Now, the whole thing is transferred to an Access table.

Pls help me convert this Excel VBA code to Access VBA.
Excel 2003 -> Access 2003

Sub GeneratePorEngID()
    Range("S2:T5000").Clear
    Dim PrevTaskID As Integer
    Dim intNO As Long
    Dim intCount As Integer
    intNO = 1
    PrevTaskID = 1
    For i = 2 To 5000
        'If PrevTaskID = 1 Then intNO = 1
 
        If Cells(i, "J") <> "" Then
            If PrevTaskID = Cells(i, "J") Then
               Cells(i, "S") = intNO
               intNO = intNO + 1
               intCount = intCount + 1
            Else
                For j = i - intCount - 1 To i - 1
                    Cells(j, "T") = intNO
                    intNO = intNO + 1
 
                Next
                Cells(i, "S") = intNO
                intNO = intNO + 1
                intCount = 0
                PrevTaskID = Cells(i, "J")
            End If
        End If
    Next
 
End Sub

Thanks a ton in advance

 

[NigelShaw]

Hi,

convert to what exactly? fields? grid? in order to get a good answer, you need to ell us what you want to happen.


N

 

[satswid]

Hi,

convert to what exactly? fields? grid? in order to get a good answer, you need to ell us what you want to happen.


N

Detailed Explanation:

I had an Excel file which is imported into an Access table now.

Now I want the same functionality, that was in the Excel file.

The given subroutine was made in Excel VBA, hence it is using Cells() and Range() to refer to cells.
However in Access tables, things are different.


The subroutine fills in the data in two blank columns, depending on the values in other columns of the same sheet.

The same functionality is needed to fill two blank fields of an Access table depending on the values in other fields of the same table.

 

[satswid]

Pls help guys

 

[gemma-the-husky]

the problem is that although Access looks like Excel, it is not excel.

ANYTHING in excel that relies on getting a value from another spreadsheet row is in principle dubious when considered within a database, and non relational

That's why you are having problem. It's probably also why no-one is likely to get involved in addressing your issue.

The real solution is to consider your requirements differently, so that the database can do what it does efficiently. (ie what it should do).