are there any VBA consultants left ? - will pay for help
in [Programming]
|
From: JLatham on 10 Apr 2010 00:40 First - the Fix!! It was a head scratcher for a file, not readily apparent unless you really dig into the code a bit. First clue: there is NOT an Option Explicit statement in the module. Second clue: in that procedure what was NOT shown in the code he put up here were 3 lines just ahead of it: For iSheetIndex = iPrevIsol To iCurrentDay strSheetName = iSheetIndex Sheets(strSheetName).Activate Because strSheetName was created 'on the fly' it took on the integer typing of iSheetIndex!! and so when Sheets(strSheetName) was used in the code, VBA interpreted it as Sheets(1) instead of Sheets("1") as intended!! Sheets(1) happens to be a worksheet named "workdata", not "1" and on "workdata", J5 is empty, so the test for = "Y or ="y" legitimately failed. It took me about a half-hour of head scratching which led to thumb sucking numbness before I figured it out. I didn't charge him any time for it -- seemed a good way to introduce myself to him, and we seem to have struck up some sort of agreement. So ain't 100% true what they say about Okies not being able to get along with Texicans!! "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 > . >
From: JLatham on 10 Apr 2010 00:44 The description and solution: http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming&mid=643f5faf-0531-4820-bc4c-a47b434196a3&sloc=en-us "Peter T" wrote: > Maybe if you describe the problem... > > Regards, > Peter T > > "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: JLatham on 10 Apr 2010 00:45 And nice of you to mention that. And DonJ_Austin does actually seem interested in not just a solution, but in learning more about VBA than he already does. As for the final assessment of damages, see http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming&mid=643f5faf-0531-4820-bc4c-a47b434196a3&sloc=en-us "B Lynn B" wrote: > I just have to say, it's really nice that you've offered compensation. It's > all too common in this forum for people who really don't want to learn VBA to > just expect others to do their work for them for free. Hats off to you! > > "DonJ_Austin" 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: Dave Peterson on 10 Apr 2010 08:29 So you did something like: Sheets(cstr(strSheetName)).Activate JLatham wrote: > > First - the Fix!! > It was a head scratcher for a file, not readily apparent unless you really > dig into the code a bit. First clue: there is NOT an Option Explicit > statement in the module. > Second clue: in that procedure what was NOT shown in the code he put up here > were 3 lines just ahead of it: > For iSheetIndex = iPrevIsol To iCurrentDay > strSheetName = iSheetIndex > Sheets(strSheetName).Activate > > Because strSheetName was created 'on the fly' it took on the integer typing > of iSheetIndex!! and so when Sheets(strSheetName) was used in the code, VBA > interpreted it as > Sheets(1) instead of Sheets("1") as intended!! > Sheets(1) happens to be a worksheet named "workdata", not "1" and on > "workdata", J5 is empty, so the test for = "Y or ="y" legitimately failed. > > It took me about a half-hour of head scratching which led to thumb sucking > numbness before I figured it out. > > I didn't charge him any time for it -- seemed a good way to introduce myself > to him, and we seem to have struck up some sort of agreement. So ain't 100% > true what they say about Okies not being able to get along with Texicans!! > > "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
From: JLatham on 10 Apr 2010 13:47 I actually took a belt, suspenders and duct tape too solution: I explicitely Dim'd strSheetName as String at the start of the module, then at the assignment statement I went with strSheetName = Trim(Str(iSheetIndex)) But cStr would have probably been the more efficient solution. "Dave Peterson" wrote: > So you did something like: > > Sheets(cstr(strSheetName)).Activate > > > > JLatham wrote: > > > > First - the Fix!! > > It was a head scratcher for a file, not readily apparent unless you really > > dig into the code a bit. First clue: there is NOT an Option Explicit > > statement in the module. > > Second clue: in that procedure what was NOT shown in the code he put up here > > were 3 lines just ahead of it: > > For iSheetIndex = iPrevIsol To iCurrentDay > > strSheetName = iSheetIndex > > Sheets(strSheetName).Activate > > > > Because strSheetName was created 'on the fly' it took on the integer typing > > of iSheetIndex!! and so when Sheets(strSheetName) was used in the code, VBA > > interpreted it as > > Sheets(1) instead of Sheets("1") as intended!! > > Sheets(1) happens to be a worksheet named "workdata", not "1" and on > > "workdata", J5 is empty, so the test for = "Y or ="y" legitimately failed. > > > > It took me about a half-hour of head scratching which led to thumb sucking > > numbness before I figured it out. > > > > I didn't charge him any time for it -- seemed a good way to introduce myself > > to him, and we seem to have struck up some sort of agreement. So ain't 100% > > true what they say about Okies not being able to get along with Texicans!! > > > > "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
|
Pages: 1 2 3 Prev: Updating Pivot Tables using a List Next: How do I insert a radio button using Excel 2007? |