+ Reply to Thread
Results 1 to 3 of 3

If then statement causing value issues in Macros

  1. #1
    ssciarrino
    Guest

    If then statement causing value issues in Macros

    I have three sheets in my workbook for journal upload form.

    Sheet Ldr Jrnl Entry is the users journal data input
    Sheet GL Upload is the Financial ERP system upload file
    Sheet Actions is the macro buttons to fire off the VB macros

    For GL Upload Sheet there is an accrual field and when this is blank it must
    be removed before uploading.

    I created this simple VB statement

    Sheets("GL Upload").Columns("T:T").Delete Shift:=xlToLeft

    The problem is that if the users have an accrual I do not want to have them
    delete this field.

    So I thought if the field is 1/01/1900 then do this:

    If Sheets("GL Upload").Columns("T:T").Value = "01/01/1900" Then
    Sheets("GL Upload").Columns("T:T").Delete Shift:=xlToLeft
    End If

    My problem is that Column T references Sheet Ldr Jrnl Entry. If you click
    any row on Column T you see this ='Ldr Jrnl Entry'!AT12 and not 01/01/1900.
    01/01/1900 only shows because of cell formatting.

    I keep getting a type mismatch error when running the If then macro..

    I am novice at best at VB and needs to guidance to how to 'trick' the
    referenced cell to be some type of value...






  2. #2
    sebastienm
    Guest

    RE: If then statement causing value issues in Macros

    Hi,
    what about comparing with the Text property of the cell instead of the Value
    property?
    If Sheets("GL Upload").Columns("T:T").Text = "01/01/1900"

    --
    Regards,
    S├ębastien
    <http://www.ondemandanalysis.com>


    "ssciarrino" wrote:

    > I have three sheets in my workbook for journal upload form.
    >
    > Sheet Ldr Jrnl Entry is the users journal data input
    > Sheet GL Upload is the Financial ERP system upload file
    > Sheet Actions is the macro buttons to fire off the VB macros
    >
    > For GL Upload Sheet there is an accrual field and when this is blank it must
    > be removed before uploading.
    >
    > I created this simple VB statement
    >
    > Sheets("GL Upload").Columns("T:T").Delete Shift:=xlToLeft
    >
    > The problem is that if the users have an accrual I do not want to have them
    > delete this field.
    >
    > So I thought if the field is 1/01/1900 then do this:
    >
    > If Sheets("GL Upload").Columns("T:T").Value = "01/01/1900" Then
    > Sheets("GL Upload").Columns("T:T").Delete Shift:=xlToLeft
    > End If
    >
    > My problem is that Column T references Sheet Ldr Jrnl Entry. If you click
    > any row on Column T you see this ='Ldr Jrnl Entry'!AT12 and not 01/01/1900.
    > 01/01/1900 only shows because of cell formatting.
    >
    > I keep getting a type mismatch error when running the If then macro..
    >
    > I am novice at best at VB and needs to guidance to how to 'trick' the
    > referenced cell to be some type of value...
    >
    >
    >
    >
    >


  3. #3
    ssciarrino
    Guest

    RE: If then statement causing value issues in Macros

    Sebastienm,

    Thanks for the heads up...well the good news...it didn't bomb with an error
    but running it does nothing..the column still exists...

    is there another value besides text, i.e. cell format or something that
    could achieve what I am looking for?

    "sebastienm" wrote:

    > Hi,
    > what about comparing with the Text property of the cell instead of the Value
    > property?
    > If Sheets("GL Upload").Columns("T:T").Text = "01/01/1900"
    >
    > --
    > Regards,
    > S├ębastien
    > <http://www.ondemandanalysis.com>
    >
    >
    > "ssciarrino" wrote:
    >
    > > I have three sheets in my workbook for journal upload form.
    > >
    > > Sheet Ldr Jrnl Entry is the users journal data input
    > > Sheet GL Upload is the Financial ERP system upload file
    > > Sheet Actions is the macro buttons to fire off the VB macros
    > >
    > > For GL Upload Sheet there is an accrual field and when this is blank it must
    > > be removed before uploading.
    > >
    > > I created this simple VB statement
    > >
    > > Sheets("GL Upload").Columns("T:T").Delete Shift:=xlToLeft
    > >
    > > The problem is that if the users have an accrual I do not want to have them
    > > delete this field.
    > >
    > > So I thought if the field is 1/01/1900 then do this:
    > >
    > > If Sheets("GL Upload").Columns("T:T").Value = "01/01/1900" Then
    > > Sheets("GL Upload").Columns("T:T").Delete Shift:=xlToLeft
    > > End If
    > >
    > > My problem is that Column T references Sheet Ldr Jrnl Entry. If you click
    > > any row on Column T you see this ='Ldr Jrnl Entry'!AT12 and not 01/01/1900.
    > > 01/01/1900 only shows because of cell formatting.
    > >
    > > I keep getting a type mismatch error when running the If then macro..
    > >
    > > I am novice at best at VB and needs to guidance to how to 'trick' the
    > > referenced cell to be some type of value...
    > >
    > >
    > >
    > >
    > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1