Prev: Updating Pivot Tables using a List
Next: How do I insert a radio button using Excel 2007?
From: Chip Pearson on 9 Apr 2010 14:40
I am a full time Office/Excel developer. Feel free to call me at (913)
549-4658.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Fri, 9 Apr 2010 09:29:01 -0700, DonJ_Austin
<DonJAustin(a)discussions.microsoft.com> wrote:

>These forums are great, but I really need some help quickly with something
>that I MUST fix. How can I find someone within hours who can help
>troubleshoot (Excel 2007 / VBA) for money ?
From: DonJ_Austin on 9 Apr 2010 17:09
Thanks to EVERYONE who reponded. To those who suggested I post more
information here, the issue is not one of syntax. When the code below is
executed, the condition is true (double-checked) but the conditional code is
not run. This same routine (and more) have been working in another place
with only one variable change, and that is been checked for existence and
spelling.

I will be following up with those who provided contact info so I can deliver
the program in question.



For iDSLineCounter = 1 To 20
If Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "Y" Or _
Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "y" Then

' *** Copy Bed Number
Sheets(strSheetName).Cells(4 + iDSLineCounter, 1).Copy
Sheets("Isolation").Cells(5 + iListCounter, 3).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
' *** Copy Patient Name
Sheets(strSheetName).Cells(4 + iDSLineCounter, 2).Copy
Sheets("Isolation").Cells(5 + iListCounter, 4).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

iListCounter = iListCounter + 1
End If

Next iDSLineCounter


"Rick Rothstein" wrote:

> If the "fix" is not too extensive, we might be able to handle it here for
> you at no cost. Can you provide more information so we can see what you
> actually need?
>
> --
> Rick (MVP - Excel)
>
>
>
> "DonJ_Austin" <DonJAustin(a)discussions.microsoft.com> wrote in message
> news:93318D54-32DF-40D0-A9BC-B88B2B3F97C4(a)microsoft.com...
> > These forums are great, but I really need some help quickly with something
> > that I MUST fix. How can I find someone within hours who can help
> > troubleshoot (Excel 2007 / VBA) for money ?
>
> .
>
From: Mike H on 9 Apr 2010 17:36
Hi,

I'm confused by the comments you make with regard to the code snippet

> When the code below is
> executed, the condition is true (double-checked)

If Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "Y" Or _
Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "y" Then

I assume you mean this 'Conditional code' is true. Setting aside that this
isn't the best way of checking for a 'Y' or 'y' in a cell when the code
executes if there is a Y or y in cell J5 (It's J5 on the first loop) of sheet
strSheetName then the next bit of code does execute and copies a couple of
cells (Paste values) to a worksheet called isolation.

What therefore is the issue?
Why is in J5 is it a Y or y or is it something else?
What cells do you think it should copy if the condition evaluates as TRUE, I
think it's A5 & B5?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"DonJ_Austin" wrote:

> Thanks to EVERYONE who reponded. To those who suggested I post more
> information here, the issue is not one of syntax. When the code below is
> executed, the condition is true (double-checked) but the conditional code is
> not run. This same routine (and more) have been working in another place
> with only one variable change, and that is been checked for existence and
> spelling.
>
> I will be following up with those who provided contact info so I can deliver
> the program in question.
>
>
>
> For iDSLineCounter = 1 To 20
> If Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "Y" Or _
> Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "y" Then
>
> ' *** Copy Bed Number
> Sheets(strSheetName).Cells(4 + iDSLineCounter, 1).Copy
> Sheets("Isolation").Cells(5 + iListCounter, 3).PasteSpecial
> Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
> ' *** Copy Patient Name
> Sheets(strSheetName).Cells(4 + iDSLineCounter, 2).Copy
> Sheets("Isolation").Cells(5 + iListCounter, 4).PasteSpecial
> Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
>
> iListCounter = iListCounter + 1
> End If
>
> Next iDSLineCounter
>
>
> "Rick Rothstein" wrote:
>
> > If the "fix" is not too extensive, we might be able to handle it here for
> > you at no cost. Can you provide more information so we can see what you
> > actually need?
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> >
> > "DonJ_Austin" <DonJAustin(a)discussions.microsoft.com> wrote in message
> > news:93318D54-32DF-40D0-A9BC-B88B2B3F97C4(a)microsoft.com...
> > > These forums are great, but I really need some help quickly with something
> > > that I MUST fix. How can I find someone within hours who can help
> > > troubleshoot (Excel 2007 / VBA) for money ?
> >
> > .
> >
From: Dave Peterson on 9 Apr 2010 17:45
I don't see anything wrong with your code. I'm gonna guess that it's your data
that doesn't match what you want -- maybe there's an extra space
(leading/trailing) in that field.

You could check it again or write your code to eliminate those extra spaces:

With Worksheets(strSheetName)
For iDSLineCounter = 1 To 20
If Trim(LCase(.Cells(4 + iDSLineCounter, 10).Value)) _
= LCase("Y") Then
' *** Copy Bed Number
.Cells(4 + iDSLineCounter, 1).Copy
Worksheets("Isolation").Cells(5 + iListCounter, 3).PasteSpecial
_
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False
' *** Copy Patient Name
.Cells(4 + iDSLineCounter, 2).Copy
Worksheets("Isolation").Cells(5 + iListCounter, 4).PasteSpecial
_
Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=True, Transpose:=False
iListCounter = iListCounter + 1
End If
Next iDSLineCounter
end with

(I added trim() and compared using lcase() on both sides of the comparison
operator.)

The with/end with saves some typing and (I think) makes it easier to read.

DonJ_Austin wrote:
>
> Thanks to EVERYONE who reponded. To those who suggested I post more
> information here, the issue is not one of syntax. When the code below is
> executed, the condition is true (double-checked) but the conditional code is
> not run. This same routine (and more) have been working in another place
> with only one variable change, and that is been checked for existence and
> spelling.
>
> I will be following up with those who provided contact info so I can deliver
> the program in question.
>
> For iDSLineCounter = 1 To 20
> If Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "Y" Or _
> Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "y" Then
>
> ' *** Copy Bed Number
> Sheets(strSheetName).Cells(4 + iDSLineCounter, 1).Copy
> Sheets("Isolation").Cells(5 + iListCounter, 3).PasteSpecial
> Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
> ' *** Copy Patient Name
> Sheets(strSheetName).Cells(4 + iDSLineCounter, 2).Copy
> Sheets("Isolation").Cells(5 + iListCounter, 4).PasteSpecial
> Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
>
> iListCounter = iListCounter + 1
> End If
>
> Next iDSLineCounter
>
> "Rick Rothstein" wrote:
>
> > If the "fix" is not too extensive, we might be able to handle it here for
> > you at no cost. Can you provide more information so we can see what you
> > actually need?
> >
> > --
> > Rick (MVP - Excel)
> >
> >
> >
> > "DonJ_Austin" <DonJAustin(a)discussions.microsoft.com> wrote in message
> > news:93318D54-32DF-40D0-A9BC-B88B2B3F97C4(a)microsoft.com...
> > > These forums are great, but I really need some help quickly with something
> > > that I MUST fix. How can I find someone within hours who can help
> > > troubleshoot (Excel 2007 / VBA) for money ?
> >
> > .
> >

--

Dave Peterson
From: Dave Peterson on 9 Apr 2010 17:56
ps. Watch for line wrap.

Dave Peterson wrote:
>
> I don't see anything wrong with your code. I'm gonna guess that it's your data
> that doesn't match what you want -- maybe there's an extra space
> (leading/trailing) in that field.
>
> You could check it again or write your code to eliminate those extra spaces:
>
> With Worksheets(strSheetName)
> For iDSLineCounter = 1 To 20
> If Trim(LCase(.Cells(4 + iDSLineCounter, 10).Value)) _
> = LCase("Y") Then
> ' *** Copy Bed Number
> .Cells(4 + iDSLineCounter, 1).Copy
> Worksheets("Isolation").Cells(5 + iListCounter, 3).PasteSpecial
> _
> Paste:=xlPasteValues, Operation:=xlNone, _
> SkipBlanks:=True, Transpose:=False
> ' *** Copy Patient Name
> .Cells(4 + iDSLineCounter, 2).Copy
> Worksheets("Isolation").Cells(5 + iListCounter, 4).PasteSpecial
> _
> Paste:=xlPasteValues, Operation:=xlNone, _
> SkipBlanks:=True, Transpose:=False
> iListCounter = iListCounter + 1
> End If
> Next iDSLineCounter
> end with
>
> (I added trim() and compared using lcase() on both sides of the comparison
> operator.)
>
> The with/end with saves some typing and (I think) makes it easier to read.
>
> DonJ_Austin wrote:
> >
> > Thanks to EVERYONE who reponded. To those who suggested I post more
> > information here, the issue is not one of syntax. When the code below is
> > executed, the condition is true (double-checked) but the conditional code is
> > not run. This same routine (and more) have been working in another place
> > with only one variable change, and that is been checked for existence and
> > spelling.
> >
> > I will be following up with those who provided contact info so I can deliver
> > the program in question.
> >
> > For iDSLineCounter = 1 To 20
> > If Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "Y" Or _
> > Sheets(strSheetName).Cells(4 + iDSLineCounter, 10) = "y" Then
> >
> > ' *** Copy Bed Number
> > Sheets(strSheetName).Cells(4 + iDSLineCounter, 1).Copy
> > Sheets("Isolation").Cells(5 + iListCounter, 3).PasteSpecial
> > Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
> > ' *** Copy Patient Name
> > Sheets(strSheetName).Cells(4 + iDSLineCounter, 2).Copy
> > Sheets("Isolation").Cells(5 + iListCounter, 4).PasteSpecial
> > Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
> >
> > iListCounter = iListCounter + 1
> > End If
> >
> > Next iDSLineCounter
> >
> > "Rick Rothstein" wrote:
> >
> > > If the "fix" is not too extensive, we might be able to handle it here for
> > > you at no cost. Can you provide more information so we can see what you
> > > actually need?
> > >
> > > --
> > > Rick (MVP - Excel)
> > >
> > >
> > >
> > > "DonJ_Austin" <DonJAustin(a)discussions.microsoft.com> wrote in message
> > > news:93318D54-32DF-40D0-A9BC-B88B2B3F97C4(a)microsoft.com...
> > > > These forums are great, but I really need some help quickly with something
> > > > that I MUST fix. How can I find someone within hours who can help
> > > > troubleshoot (Excel 2007 / VBA) for money ?
> > >
> > > .
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Updating Pivot Tables using a List
Next: How do I insert a radio button using Excel 2007?