Convert Excel formula to VB

[adriaandl]

Hi,

I am new to VB and would like to know how I can change my excel formula to VB?
this is my formula:
=IF(A3="Telkom","TGE",IF(ISNUMBER(SEARCH("mobile",A3)),"MOB",IF(ISNUMBER(SEARCH("ambulance",A3)),"TSC",IF(ISNUMBER(SEARCH("toll free n",A3)),"TSC",IF(ISNUMBER(SEARCH("toll free p",A3)),"TSC",IF(ISNUMBER(SEARCH("telkom spec",A3)),"TSC",IF(ISNUMBER(SEARCH("telkom univ",A3)),"TNG",IF(ISNUMBER(SEARCH(" e toll free",A3)),"TNG","OLO"))))))))

Any help is appreciated.

Adriaan.

 

[RCBricker]

the best way to learn how to do this is to use the macro recorder. Simply record entering the formula into a cell.

That is how I learned.

Also, are you planning on replacing cell references with variables?

rich

 

[adriaandl]

I have a spreadsheet with Columns A:H
I am looking for the string in Column A and if found then adding the descriptions in column H
So is Column A = Telkom then Column H = TGE
I have variable sheet lengths.

Here is an example of my Sheet:

A B C D E F G H
[TABLE="width: 668"]
<colgroup><col><col span="4"><col><col span="2"></colgroup><tbody>[TR]
[TD]Cell C Mobile
[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]2784[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]MOB[/TD]
[/TR]
[TR]
[TD]MTN Mobile[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]810[/TD]
[TD="align: right"]27810[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-01-2015[/TD]
[TD]Neotel[/TD]
[TD]MOB[/TD]
[/TR]
[TR]
[TD]Telkom Mobile[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]614[/TD]
[TD="align: right"]27614[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]MOB[/TD]
[/TR]
[TR]
[TD]Vodacom Mobile[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]818[/TD]
[TD="align: right"]27818[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]MOB[/TD]
[/TR]
[TR]
[TD]45 Degrees[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]10610[/TD]
[TD="align: right"]2710610[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]OLO[/TD]
[/TR]
[TR]
[TD]Amobia[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]210061[/TD]
[TD="align: right"]27210061[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]OLO[/TD]
[/TR]
[TR]
[TD]Telkom[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2710[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]TGE[/TD]
[/TR]
[TR]
[TD]Telkom Universal Services[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]89900[/TD]
[TD="align: right"]2789900[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]TNG[/TD]
[/TR]
[TR]
[TD]Toll Free National Emergency Service[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]TSC[/TD]
[/TR]
[TR]
[TD]Telkom Special codes[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]1020[/TD]
[TD="align: right"]1020[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]TSC[/TD]
[/TR]
[TR]
[TD]toll free Police Emergency
[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]10111[/TD]
[TD="align: right"]10111[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]TSC[/TD]
[/TR]
[TR]
[TD]Ambulance Emergency Service
[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]10177[/TD]
[TD="align: right"]10177[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]01-10-2014[/TD]
[TD]Neotel[/TD]
[TD]TSC[/TD]
[/TR]
</tbody>[/TABLE]

 

[RCBricker]

As I said, the best way to learn this in VBA is to use the recorder. It will show you what the formula should look like when it is in VBA.

do you want the formula in column H or just the results?


Rich

 

[adriaandl]

Hi Rich,

The formula in my sheet is in column H as that is where i want the results to be.
I have used the recorder but it only records what happens in cell H2
I need the VB script to use the active sheet and looking for the text in column A then giving the results in column H
Recording the macro does not show me how to write the script to run from first row to last row

Adriaan

 

[adriaandl]

Hi Rich,

This is what I get when I record the Macro:
Sub Macro4()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+r
'
ActiveCell.FormulaR1C1 = _
"=IF(RC[-8]=""Telkom"",""TGE"",IF(ISNUMBER(SEARCH(""mobile"",RC[-8])),""MOB"",IF(ISNUMBER(SEARCH(""ambulance"",RC[-8])),""TSC"",IF(ISNUMBER(SEARCH(""toll free n"",RC[-8])),""TSC"",IF(ISNUMBER(SEARCH(""toll free p"",RC[-8])),""TSC"",IF(ISNUMBER(SEARCH(""telkom spec"",RC[-8])),""TSC"",IF(ISNUMBER(SEARCH(""telkom univ"",RC[-8])),""TNG"",IF(ISNUMBER(SEARCH("" e toll free"",RC[-8])),""TNG"",""OLO""))))))))"
Range("H2").Select
Selection.Copy
Range("H3:H2320").Select
End Sub

How do I change this to apply to the whole sheet?

Adriaan

 

[RCBricker]

ok that is what I started to think that you wanted.

something like...

Sub checkdata()

Dim wb1 As Workbook
Dim sh1 As Worksheet
Dim rng As Range, cell As Range, rngSOURCE As Range, _
    rng1 As Range, cell1 As Range, rngwb2dates As Range
Dim mydate As String, mydate2 As String, mytime As String
Dim lrow As Long
Dim i As String, j As String, k As String, l As String, _
    m As String, n As String, o As String, p As String
    
    Set wb1 = ThisWorkbook 'wb1 is the workbook with the code

    Set sh1 = wb1.Sheets("Sheet1")
    

    With sh1
        lrow = Range("A" & .Rows.Count).End(xlUp).Row
        Set rng = Range(.Cells(1, 1), .Cells(lrow, 1))
        For Each cell In rng
                i = InStr(1, UCase(cell.Value), "TELKOM")
                j = InStr(1, UCase(cell.Value), "MOBILE")
                k = InStr(1, UCase(cell.Value), "AMBULANCE")
                l = InStr(1, UCase(cell.Value), "TOLL FREE N")
                m = InStr(1, UCase(cell.Value), "TOLL FREE P")
                n = InStr(1, UCase(cell.Value), "TELKOM SPEC")
                o = InStr(1, UCase(cell.Value), "TELKOM UNIV")
                p = InStr(1, UCase(cell.Value), " E TOLL FREE")
            If i > 0 And n = 0 And o = 0 And j = 0 Then
                cell.Offset(, 7).Value = "TGE"
            Else
                If j > 0 Then
                    cell.Offset(, 7).Value = "MOB"
                Else
                    If k > 0 Or l > 0 Or m > 0 Or n > 0 Then
                        cell.Offset(, 7).Value = "TSC"
                    Else
                        If o > 0 Or p > 0 Then
                            cell.Offset(, 7).Value = "TNG"
                        Else
                            cell.Offset(, 7).Value = "OLO"
                        End If
                    End If
                End If
            End If
        Next cell
    End With

End Sub

RICH

 

[Special-K99]

Am no VBA expert but does this work:

MyResult=""
For i=1 to 12
If Instr(WorkSheets("Sheet1").Cells(i,1),Range("A3")) then
    MyResult=Cells(i,12)
    Exit For
EndIf
Next i

where your table is on Sheet1!A1
and MyResult is either blank or the value in the table

 

[adriaandl]

Hi Rich,

Thank you that's what I am looking for.
Just one question, I have a header row, so would only start from row 2.

Adriaan

 

[adriaandl]

Hi Rich,

I have found the answer, I have changed this line:
Set rng = Range(.Cells(2, 1), .Cells(lrow, 1))

Now it starts from row 2

Thank you for your help.
Adriaan