Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: EXTRA ZERO

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I Have a worksheet where in column a there are productnumbers like 045444 but excel doesnt; show the first zero so i had to make a format like 000000
    now in vba i want to take connect to an sql server and find the description which belongs to 045444 but activecell.text gives 045444 that;s what i want to lookup as a value but this is now a text and activecell.value gives 45444 (but i need the extra zero)how can i look up 045444 as a value in my sql databse. ?

    can someone help

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you connecting using VBA ? if so, can you post your current code ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yes i connect through vba and this is my code
    y should be the value with the zero in front

    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 7-3-2002 by Plieger
    bnumber = 0

    Do While ActiveCell.Value <> 99999
    bnumber = bnumber + 1
    x = "b" & bnumber
    With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=proddta;", _
    Destination:=Range(x))
    y = ActiveCell.Text
    .CommandText = Array( _
    "SELECT f4101.imdsc1" & Chr(13) & "" & Chr(10) & "FROM AS400GOFI.PRODDTA.f4101 f4101" & Chr(13) & "" & _
    Chr(10) & "WHERE (f4101.imlitm = " & y & ")" _
    )
    .Name = "Query from proddta"
    .FieldNames = False
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    If ActiveCell.Value = "" Or ActiveCell.Value = "niet leverbaar" Then GoTo verder1
    If ActiveCell.Value <> "" Or ActiveCell.Value <> "niet leverbaar" Then GoTo verder
    verder1:
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    bnumber = bnumber + 1
    GoTo verder:
    verder:
    Loop
    End Sub

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi herman

    how about

    y = Format(CInt(y), "00000")
    If IsNumeric(y) Then MsgBox Len(y)



  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The code generates an overflow

    i don't know what the code exactly does but i have a text and want a number
    does cint convert text to number ??

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes it does, you might need Clng Function if you are getting an Overflow.



  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanx for your support it's working.

    Greetings.
    Herman

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •