Posts by Goppi

Re: VBA Find function sometimes not working

Quote from StephenR;608344

Specifying all the parameters might solve the problem. If you record a macro of using Find it will generate all the necessary syntax which you can then paste into your code. If that doesn't do it, a sample workbook would help.

Hi Stephen,

Thanks for your reply. I tried adding some of the parameters that I though could make a difference, but not all. I'll look into this and will try some more. A sample workbook unfortunately won't help. I have about 10 Excel files with heavy VBA code which are run every day and those have hundreds of Find functions - none of those are causing any problems. Even if I take the one file that currently causes sometimes an issue and I try it on my PC, I can't replicate the issue. The only difference is that on the one PC where this phenomena appears, a few other VBA programs are executed beforehand. Hence my thought that Excel might keep some Find parameters in the memory or that Excel can't handle that many Find calls...

I'll try to specify more parameters and see if it makes any difference.

I welcome any other hints, ideas or background knowledge on how Excel managaes Find methods, results and parameters

Thanks
Goppi

Re: Modified Formula/Macro for Text

Assuming you want to indicate with your last sentence that you need to use Option 1 as per jindon, aka you copy the number and want to paste a text with the pre- and post-fix - how do you want the vba code to distinguish between fax numbers and any other text you copy/paste into Excel?

Hi,

To make the story short, the find function sometimes doesn't find a the value in a range though it's there. However if executed on a different PC, it might find the value. I'm assuming it has to do with a state fo the Find feature that Excel keeps from a previous find (similiar to pasting text into Excel that based on previous text to column settings might or might not be correctly split into multiple cells.)

A typical VBA code I'm using looks like this:

Set ws = wb.Worksheets("CO")
Set ws2 = ActiveWorkbook.Sheets(1)
nlastrow = FindLastRow(ws)


With ws2
  For i = nlastrow to 2 Step -1
     s = .cells(i,2)
     Set c = ws.Columns(1).Find(s, Lookat:=xlWhole)
     If c is Nothing Then
     ...
     End If
  Next i
End With

Long Story - the VBA codes (multiple files) are executed every single day since about 2 yrs without a problem. About 3 months ago, suddenly one of the Find functions didn't find the cell anymore. The same happened on a second PC at exactly the same position in the code at exactly the same value - but not on a third PC. Back then due to time constraints, but also the small size of the repetitions I decided to implement a work-around by using a loop instead of getting to the bottom of this. The problem moved than from one Find code to another one in the same file. I used the same work-around and we didn't experience any problems until 2 weeks ago. Since 2 weeks it happened only on two days and only on one PC that the Find function again doesn't seem to find the value. When I ran the same code on a different PC on the same data, no issues. Thsi time, however I would delay the whole process quite a lot if I would implement the work-around and I probably only move the issue further to the next Find call. Hence I'm trying to find out what the cause could be.

Did anybody come across such an intermittend behaviour? Might this be related to previously executed Find functions/methods?

Thanks in advance,
Goppi

Re: Slow Update Of Edited Links

It's not a Hyperlink nor a code that updates the values. It's a simple link between Excel files.

Yes, the user replaces the folder in the link by Edit>Replace.
I thought it might make a difference by replacing the folder-name by a VBA code, but no, it didn't. It seems that Excel is verifying every single link when changed. And that doesn't change when done by a macro. I'll think about alternatives, but also if it's possible somehow to stop the validation/updating mechanism in Excel until all links have been changed and than update them all at once.

Thanks for your comments.

My business has a significant number of Excel sheets interlinked. One of them has about 10.000 links to about 60 files. Those 60 files are within a sub-folder and once a quarter the business needs to replace all 60 files with a new set. To establish this, they replace the sub-folder name in the link with the new name. To do this on 10.000 links takes about 72 minutes because Excel seems to open up a file for every updated cell. We tried to stop this by changing Excel to calculate manual, disabled "Update remote reference", but this didn't improve anything.

We have similiar issue when opening/updating the master file - it takes around 3 mins.

Besides that a proper database is certainly better for this requirement, is there anything we can do to improve the performance of the replace method. I was thnking about stopping Excel from validating/updating each single cell and after everything has been replaced to do it in one go without Excel opening and closing 10.000 times a file.

Thanks,
Goppi

Re: Remembering And Setting Position Of Commandbar

Quote from Andy Pope

go to the object browser and search for CommandBar
You will see the object as a bunch of properties you could check and apply to the new commandbar.
Stuff like left, top, Position etc.

I guess my question deserves this kind of answer. I was hoping that somebody had some well thought-out code already in use. Otherwise I will have to play around with Left and Top.

cheers,
Goppi

Hi everybody,

I have a few Excel 2003 worksheets with custom commandbars. I usually create them in VBA during the opening of the workbook. In case the commandbar is still there due to f.i. a fault in the code or anything else, I first delete it. So a typical sequence in The Workbook_Open sub looks like:

On Error Resume Next
CommandBars("Test").Delete
On Error GoTo 0
Set cb = CommandBars.Add("Test", msoBarTop, False, True)
With cb
  ...
  .Visible = True
End With

The issue with this is that when I add the commandbar, it just adds it at the bottom of the top bar and not where I moved it the last time I used the file. I also have one excel workbook that creates multiple commandbars in the Top bar - but one under each other - ideally I would like to have them positioned next to each other.

Is there any attribute that I could read out before deleting the commandbar and set when I create it?

cheers,
Goppi

Re: Apostrophe Causes Error

It's not VBA that has the issue with recognizing the string correctly, but the function GetObjects when it processes the string. Therefore the chr function wouldn't make any difference - or in other words if you test this in the Locals window you will see that the string with and the one without the Chr function gives exactly the same results.

Goppi

Re: Apostrophe Causes Error

Hi, the issue you describe is unrelated to the one I have. I'm not writing anything into a cell with the getobjects method. That would come later. But the Getobjects method causes an error because it thinks that the first parameter has finished wiht the second apostrophe. Or in other words this is how the string should be interpreted:

Exchange_Mailbox.LegacyDN = '/O=TEST/OU=TEST/cn=Recipients/cn=co'brien'

... basically the text between LegacyDN and the next parameter as one string - but this is how it does inteprete it:

Exchange_Mailbox.LegacyDN = '/O=TEST/OU=TEST/cn=Recipients/cn=co'

which leaves the text " brien' " before the next parameter starts and this causes the issue.

goppi

Hi,

I use the GetObjects function to gain access to the properties of a mailbox and want to fill out a table with some detail information. The code works fine until I have a mailbox which name has an apostrophe included.

set o = getobjects("winMgmts:!\\VS1\root\MicrosoftExchangeV2:Exchange_Mailbox.LegacyDN=[B]'/O=TEST/OU=TEST/cn=Recipients/cn=co'brien'[/B],MailboxGUID='{59A40254-9F12-4502-9A03-3FB5CB0BBD38}',ServerName='VS1',StorageGroupName='sg1',StoreName='mb1'")

I highlighted the section that causes the problem. The string within the string is encapsulated in apostrophes, hence the apostrophe in the mailbox name co'brien confuses the code and raises an error. Microsoft provides a solution by using escape characters, but that seems to work only in vbscript, not in vba. Here is a link to Microsoft's solution:

http://www.microsoft.com/technet/scriptcenter/resources/qanda/dec04/hey1217.mspx

I tried some workarounds that are being used for a similiar problem with SQL statements as well, but again without success - these are the versions I tried:

co/'brien
co\'brien
co''brien

Does anybody have some experience with this?

Thanks,
goppi

Re: Getopenfilename Runtime Error 13

you are welcome. : D

Goppi

Re: Vba To Automate Formula Application

sorry, didn't see that.

try this out:

Sub CalcRows()
  Dim nLastRow
  
  nLastRow = LastRow("Data")
  
  If nLastRow > 1 Then
    With Worksheets("Formula Sheet")
      For i = 2 To nLastRow
        .Range("C3") = Worksheets("Data").Cells(i, 2)
        .Range("C4") = Worksheets("Data").Cells(i, 3)
        .Range("C5") = Worksheets("Data").Cells(i, 4)
        Worksheets("Data").Cells(i, 5) = .Range("C10")
      Next
    End With
  End If
End Sub


Function LastRow(s As String)
    On Error Resume Next
    LastRow = Worksheets(s).Cells.Find(What:="*", _
    After:=Worksheets(s).Range("A1"), _
    Lookat:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
    On Error GoTo 0
End Function

Goppi

Re: Getopenfilename Runtime Error 13

Try that - I shortend it quite a bit by the code that either got neve executed or was useless.

Sub GetData_Example5()
  Dim SaveDriveDir As String, MyPath As String
  Dim FName As Variant,
  Dim destrange As Range
  Dim sh As Worksheet
  Dim wsNew As Worksheet
     
  SaveDriveDir = CurDir
  MyPath = Application.DefaultFilePath 'or use "C:\Data"
  ChDrive MyPath
  ChDir MyPath
  FName = Application.GetOpenFilename(filefilter:="Excel Files,*.xls", _
    MultiSelect:=False)
  If FName = False Then
   ' They pressed Cancel
   ' Set Label Caption as Cancel so rest of routine can be Canceled
    UserForm14.Label24.Caption = "Cancel"
    Exit Sub
  End If
  If wsExists("QRL") Then ' This routine deletes the sheet QRL if its there
    Sheets("QRL").Delete
  End If
  Application.DisplayAlerts = True
  Application.ScreenUpdating = False
  'Add worksheet to the Activeworkbook and use the Date/Time as name
  Set sh = ActiveWorkbook.Worksheets.Add
  sh.Name = "QRL"
       
  'create the destination cell address
  Set destrange = sh.Cells(1, "A")
               
  ' For testing Copy the workbook name in Column E
  'sh.Cells(rnum + 1, "E").Value = FName(N)
             
  'Get the cell values and copy it in the destrange
  'Change the Sheet name and range as you like
  GetData FName, "QRL", "A:H", destrange, False, False
  ChDrive SaveDriveDir
  ChDir SaveDriveDir
  Application.ScreenUpdating = True
    
' If the sheet QRL is in this workbook then it moves
    ' it behind the data tab
  Sheets("QRL").Select
  Sheets("QRL").Move Before:=Sheets(4)
End Sub

Re: Getopenfilename Runtime Error 13

one more thing in that case - don't use
If FName = "False" Then
but
If FName = False Then

False is a VBA constant and not a string.

As far as I could see it works fine - what do you expect it to do?

Since you mentioned that you copied the code - perhaps it's doing something else than what you expect. To get it working on my PC I had to rem out a few lines which are using functions that I don't have.

Goppi

Re: Sum Where Corresponding Row is Higher

Quote from StephenR

Try this:

* signifies 'AND' and I think + signifies 'OR'

Cool, works fine. Is there somewhere a list of which functions don't work with Array Formulas?

Thx,
goppi

Re: Tab Between Controls

Quote from Dave Hawley

Yes, but can you see them without clicking in the said cells?

No, the SpecialEffect is set to flat in the prperties of the controls.

Re: Getopenfilename Runtime Error 13

Hi Joe,

I thought I take the time and comment all your ideas + add the solution:

Quote

If I change the Dim FName as a String for instance it then errors in the middle bold area "If IsArray(FName) Then" as a Compile Error - Expected Array.

Since you want the user to be able to select multiple files, you will receive an array - To Dim FNam as a String wouldn't work.

Quote

I tried deleting the Option Explicit but that didn't do anything.

Option Explicit is a directive for the VBA intepretar. All what it says is that any used variable has to be explicitly dimensioned. If you leave this option away, you just won't have to dimension variables. Excel could do it automatically for you.

You can solve the issue by replacing the row If FName = "False" Then
with If Not IsArray(FName) Then

Re Caption, If you define a variable in the Userform, you can change that variable instead of the Caption.

goppi

Re: Tab Between Controls

Quote from Dave Hawley

That's odd, they don't show for me.

On my PC I found them in A2 and B2 next to the text.

Re: Error: Copy Method Of Worksheet Class Failed

Is it possible to post your vba code? Otherwise I can start uessing - but that won't be of much help

Goppi

Re: Vba To Automate Formula Application

Scrooge,

The easiest solution would be to delete the "Formula sheet" and to enter in E2 the formula =(B2+C2)*(D2+1)/B2
You can than fill the column with this formula...

Goppi