how to write excel vba formula into R

General
1

Hi team,

I need your help,

bold numbers all are columns

For x = 2 To lr

For Z =  **0**  To counter - 1

    task1 = dbsheet.Cells(x + Z,  **3** )

    task2 = dbsheet.Cells(x + Z,  **4** )

    task3 = dbsheet.Cells(x + Z,  **8** )

    task4 = dbsheet.Cells(x + Z,  **28** )

    ppc1 = Left(dbsheet.Cells(x + Z,  **6** ),  **11** )

    If (task1 = "Artwork Creation" Or task1 = "ARTWORK REJECTED Artwork Creation") And task3 = "Completed" Then

        sdate = dbsheet.Cells(x + Z,  **9** )

        ppc = dbsheet.Cells(x + Z,  **7** )

        If task4 = "" Then

            Worksheets(actsheet).Cells(x + Z,  **28** ).Value = "FTPR"

        End If

        If (dbsheet.Cells(x + Z +  **1** ,  **3** ) = "Artwork Studio Check") And (Left(dbsheet.Cells(x + Z + 1,  **6** ),  **11** ) = ppc1) Then

            If (dbsheet.Cells(x + Z +  **2** ,  **3** ) = "REWORK REQUIRED") And (dbsheet.Cells(x + Z + 2,  **4** ) = "Artwork Creation") And (Left(dbsheet.Cells(x + Z +  **2** ,  **6** ),  **11** ) = ppc1) Then

                If dbsheet.Cells(x + Z +  **2** ,  **28** ) = "" Then

                    Worksheets(actsheet).Cells(x + Z +  **1** ,  **28** ).Value = ""

                    Worksheets(actsheet).Cells(x + Z +  **2** ,  **28** ).Value = "REJ"

                End If

            End If

            If dbsheet.Cells(x + Z +  **3** ,  **3** ) = "Artwork Studio Check" And (Left(dbsheet.Cells(x + Z +  **3** ,  **6** ),  **11** ) = ppc1) Then

                If (dbsheet.Cells(x + Z +  **4** ,  **3** ) = "REWORK REQUIRED") And (dbsheet.Cells(x + Z +  **4** ,  **4** ) = "Artwork Creation") And (Left(dbsheet.Cells(x + Z +  **4** ,  **6** ),  **11** ) = ppc1) And dbsheet.Cells(x + Z +  **4** ,  **8** ) = "Completed" Then

                    If dbsheet.Cells(x + Z +  **1** ,  **28** ) = "" Then

                        Worksheets(actsheet).Cells(x + Z +  **1** ,  **28** ).Value = "PRMISS"

                        Worksheets(actsheet).Cells(x + Z +  **3** ,  **28** ).Value = ""

                    End If

                End If

            End If

        End If

        If (dbsheet.Cells(x + Z + 1, 3) = "REWORK REQUIRED" And dbsheet.Cells(x + Z + 1, 4) = "Brief Submission") And dbsheet.Cells(x + Z + 1, 7) = ppc And dbsheet.Cells(x + Z, 28) <> "Brief Rej" Then

            Worksheets(actsheet).Cells(x + Z, 28).Value = "Brief Rej"

        End If

    End If

Next Z

x = x + counter - 1

counter = 0

Next x

Regards
Dinesh Kumar

2

Could you provide example of what this is supposed to do?

An example of the data before and after would be useful to find an equivalent R solution. Nested ifs four deep are difficult to guess at what's going on without a working example to examine.

3

image
image3788×241 56.6 KB

4

Try CSV export

I'm sorry. I can't make out anything from that images, and images in general. Exporting the example rows to csv and then sharing in some fashion that could be read in or at least be copy pasted would be good.

Some convenient sites for sharing code and data are:

If the above are unavailable, a last resort would putting the data in a post in this thread. At least plain text is a bit more tractable than images.

Read data into R and emit as a reproducible example

See this post for details about how to make reproducible examples. In short, some R code that gets the readers to the starting point of the problem is the most likely to get a useful answer.

5

Plese find the above link

6

Is that the pre-process data or the example of what the data should look like after it's processed?

What have you tried so far in R?

7

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.