Convert VBA to Google Script

griggsa14

New Member
Joined
May 27, 2019
Messages
11
I know this may be a large ask and may not be possible but I am new to VBA and never really used sheets before in a very elaborate fashion. I didn't realize that the excel sheet with vba wouldn't translate to sheets so I need to convert my vba code to google script so that it will work in sheets. I need to convert my vba code that allows a multiselection dropdown as well as a cut and past from one tab to another based on a specific condition in Column A. Column H is the multi selection drop down. The other part of the code that cuts is when A says completed, denied, mistake-abandoned it should cut the whole row and put it in the corresponding tab of the same name. The code below works perfectly in my excel sheet but I have no idea how to translate it to google script.
Code:
[FONT=inherit]PrivateSubWorksheet_Change(ByValTargetAsRange)
  Dim R AsRange, i AsLong, sh AsWorksheet

  OnErrorGoToExitsub

  Set R =Intersect(Range("A:A"),Target)

  Application.EnableEvents=False
  IfNot R IsNothingThen
    For i = R.CountTo1Step-1
        SelectCase R(i).Value
            Case"Completed":Set sh =Sheets("Completed")
            Case"Denied":Set sh =Sheets("Denied")
            Case"Mistake-Abandoned":Set sh =Sheets("Mistake-Abandoned")
        EndSelect
        With R(i).EntireRow
            .Copy sh.Cells(sh.Rows.Count,"A").End(xlUp).Offset(1,0)
            .Delete
        EndWith
    Next i
  EndIf
  Application.EnableEvents=True

  DimOldvalueAsString
  DimNewvalueAsString

  IfNotIntersect(Target,Range("H:H"))IsNothingThen
    IfTarget.SpecialCells(xlCellTypeAllValidation)IsNothingThen
        GoToExitsub
    Else
        IfTarget.Value=""ThenGoToExitsubElse

        Application.EnableEvents=False
        Newvalue=Target.Value
        Application.Undo
        Oldvalue=Target.Value
        IfOldvalue=""Then
            Target.Value=Newvalue
        Else
            Target.Value=Oldvalue&", "&Newvalue
        EndIf
    EndIf
  EndIf
  Application.EnableEvents=True
Exitsub:
  Application.EnableEvents=True
EndSub[/FONT]
 
Last edited by a moderator:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top