Code transposition from Excel Formula

[Rebellion34]

All,

I created a formula in Excel to allow me to scan in tracking numbers from FedEx and UPS that will display only the 12 digit tracking # from Fedex from the 36 digits created by the barcode but will leave the UPS style to display as is which works as long as I use 2 cells to make this transition. what I would like to have is a VB code that will allow this formula to work so that each # displays in the active cell i.e. the cell that I scan into, I know this is not possible using formula's as when the new info is entered the formula is overwritten and therefore is pointless. here is the formula can someone show me how to write this as VB please?

[blue]=IF(B1<"1z99999999999999999",B1,CONCATENATE("",MID(B1,17,12)))[/blue]

thanks in advance.

Rob

 

[SkipVought]

Hi,

Sub test()
'=IF(B1<"1z99999999999999999",B1,CONCATENATE("",MID(B1,17,12)))
    With ActiveCell
        If .Value < "1z99999999999999999" Then
            .Value = "'" & Mid(.Value, 17, 21)
        End If
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

myResult = IIf(myVar < "1z99999999999999999", myVar, "'" & Mid(myVar, 17, 12))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

If you want this as a variable...

Sub test()
'=IF(B1<"1z99999999999999999",B1,CONCATENATE("",MID(B1,17,12)))
    Dim MyValue As String
    With ActiveCell
        If .Value < "1z99999999999999999" Then
            MyValue = "'" & Mid(.Value, 17, 21)
        End If
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rebellion34]

HI Skip,

I guess I am doing something wrong I copied your code into a module and scanned in a fedex tracking# and it didn't remove the unwanted part of the 32 digits that appear when these are scanned, can you shed some light and point me on the correct path to obtain the results your code created for please.

many thanks for taking the time to assist me

Rob

 

[SkipVought]

Post an example of the entire number and then the portion that you want to return.

Explain EXACTLY what you are doing, ie

1. scan the number to cell X1
2. run the macro
3. observe the new value in WHERE???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Did you try my suggestrion ?

 

[Rebellion34]

Skip,

I am trying to set up a sheet that will allow me to use a hand scanner to scan a tracking # from either FedEx or UPS into a cell and have the code make that information display in the same cell, the cell location will be a range which I am using column B:B.

The number will be a random 32 digit number from a FedEx barcode 95621547865235842139874562874526 the portion in red is what I need to have displayed in the active cell that I scan into.

I will also need an exception set so that if I scan a UPS tracking # it will remain constant to what scans in e.g. 1Z9999999999999999.
Cell A1 displayed cellA1 scanned
919386258943 4030789913010249193862589434309
CellA1 displayed CellA1 Scanned
1Z2571440130076754 1Z25714400130076754

The following formula entered into a spreadsheet works but you need to scan into cell b1 to display into a1, b2 for a2 etc.
=IF(B1<"1z99999999999999999",B1,CONCATENATE("",MID(B1,17,12)))
My intention is to do away with the need for 2 cells and reduce the double entry affect it causes.
Thanks
Rob

 

[Rebellion34]

PHV,

I tried your suggestion also but what scanned into the active cell was what displayed also it did not concatenate the number to show the portion I needed,

Thanks

Rob

Skip I forgot to color code that number 95621547865235842139874562874526

 

[SkipVought]

First of all the value that you posted is 32 characters in length.

your MID function is to return 21 characters, starting at character 17. ????? Ain't that many.

second, only the LAST of the values you posted pass the IF test.

???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Rebellion34]

Skip,

Correct initial value that will be concatenatedis 32 charachers in length the intial part of the formula enabless an 18 character value that is less than 1Z9999999999999999 to be displayed without alteration.

The MID funtion it to display 12 characters starting at 17 ending at 28,(MID,17,12)))

Is there a way that I can post a spreadsheet with the working formula that I currently have?

thanks

Rob

 

[SkipVought]

What is the problem?

What exact code are you currently using?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

What about this in the Worksheet_Change event procedure?

With Target
  If .Column = 1 'Scan result in column A
    If .Value > "1z99999999999999999" Then
      Application.EnableEvents = False
      .Value = Mid(.Value, 17, 12)
      Application.EnableEvents = True
    End If
  End If
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rebellion34]

Skip

not sure if this will assist but I recorded a macro of the formula that I enter into Cell A1 so that as I scan the 32 digit number into Cell B1 it will concatenate that into the mid range of 12 characters I need starting at 17 through 28. this formula means that I need to scan into column B to have the result in Column A, what I need is to scan into colomn A1 and have the result show in coloumn A1 and respective through column A. wherever I am in column A as the active cell is where the corrected Number will display. here is the recorded macro.
Sub Macro7()
'
' Macro7 Macro
'

'
ActiveCell.FormulaR1C1 = _
"=IF(B1<""1Z9999999999999999"",B1,CONCATENATE("""",MID(B1,17,12)))"
End Sub

Thanks

Rob

 

[PHV]

Rob, did you try my suggestion stamped 22 Jun 09 21:45 ?