EXTRA ZERO
Find bottlenecks in your Excel workbooks
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

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
  •  

 

 
DMCA.com