Replies

Sorry about the delay in replying but please offer suggestions re the code in the attached book.

Thanks Hans – Firstly I meant Name is a heading. It refers to a business name that is extracted from the finance app. There can be about 500 on each extracted sheet and I need to find each so that I can then copy the business name (next cell to right of heading Name )

BTW, I did explore that code but my range is not defined. Sometimes it will be 5000 rows and other times it will be 3000 rows.

I have tried UsedRange but it doesn’t seem to define the range correctly. I couldn’t get it to work with my code.

As a test I did try the following code to see if I was getting closer but it also stops when it has run out of text to find and replace.

Sub FindNext()
With Worksheets(1).Range("a1:a5000")
    Set c = .Find("Name", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = "FoundName"
            Set c = .FindNext©
        Loop While Not c Is Nothing And c.Address  firstAddress
    End If
End With
End Sub

At the mercy of the court…

Alan, Chris, Don and Kim – the good news is that it now runs the way it should. While the efforts of everyone who offered their suggestions, recommendations were very much appreciated, it was Hans simple response that nailed it. I had looked at a similar solution very early (nearly two days of Googling, trying code and hairtearing) but I couldn’t get it to work…
|
|
|
– And then it dawned on me, the format of the INI file was critical. Single-stepping the code was coming up with no response for the bit where it had to get the string from the INI. I found out that there has to be no ‘blank’ entries in the Keys otherwise it just goes with a default value (none given in this case). I overcame it with a simple ‘Reserved’ in the blanks and behold, it worked flawlessly.

Thanks again all, and to Hans, who seems to always find an answer for me, many thanks. Very much appreciated.

As promised I will put this into a small package and post it here for others to use. That is if they haven’t put a few bits of code together and come up with their own answer.

Thanks Kim and Don and Hans

If I can’t get another way to resolve this I might have to resort to invoking Word. But as Hans said, I believe it increases the overhead and I would like to keep it down.

Battling on…

Thanks for your input Chris – However I need the INI file to be untouched as it contains data that the users need to use but not modify. I can’t have any chance of the INI being deleted therefore the WritePrivateProfileString function will not not be declared.

You might like to have a look at what I used originally and offer suggestions based on that though (ZIP file in my response to Alan)

Hi Alan – I had a look at the vbAccelerator site and tried to work with the CLS file but got error message about the form class not being supported in VBE.

However if anyone can see the way to use the function GetPrivateProfileString to work where I have used GetSetting in my attached example INI and code, I would be immensely grateful.

Thanks again Jefferson but the path I am looking at is to have a totally text file of the format of standard INIs without reference to Registry keys.

Not quite sure what is meant by building the data into an array. If it makes it clearer for you I would have an INI file with a structure that includes Sections and Keys (with Keynames) that my Excle VBA code would attempt to get information from using the GetPrivateProfileString function. Some of this data would go into a textbox and other data would populate a listbox dependent on which option button the user clicked on the form.

If I can get the function to work correctly it will perform a similar function (‘scuse the pun) to what the GetSetting function does in importing data from relevant keys in the Registry.

At the moment it is not working but I’ll keep going as I need it to work…

Thanks for your input Jefferson. A REG is indeed a file (e.g. MyFile.REG) that is merged into the Registry.

The purpose of the INI (basically a text file) is that it can have a structure of Sections, Keys and strings of information that my VBA code will call from the INI file when a user clicks on individual option buttons in the form. The data then populates text or listboxes for further choices for the user.

A CSV is basically a text file also but I have not seen them used in the same way that I believe I require. I think an INI file is the way I must pursue.

If I am successful with what I am doing presently, I will post it for others to consider because I cannot find any other references to it on the ‘net.

Regretably, despite good advice from Hans, my skills are falling short on this one.

Can someone tell me more about the sort of relationships that should be established for multiple tables to work as I intended?

I.e., the form will be for data input and a query is used to enable the form to be created. Is that clear or make sense? The tables are going to be 7 in number (incl. the main table) and there will be way too many fields required in each to have a single table. The form will be multi-tabbed using the tab control as I have laboured previously with a form that had multiple controls placed one over another and using code to hide or display the relevant control.

Attached is the non-working version where I have played with relationships.

Any assistance to resolve my dilemma will be appreciated.

Good – using a tabbed form was the way I was going to go in view of the large number of fields. It was getting messy using using code to hide various year levels on the original form and a devil when any form field needed a minor adjustment.

Thanks again Hans

Thanks for your reply Hans

I had a bit of a play – as you do – and new db attached seems to work.

However, any comments on the relationships would be appreciated.

BTW, it currently only has one extra table but the others will be along the same lines as tblResults.

TIA, Leigh

Thanks Hans, again your guidance and code has worked a treat. I do appreciate your assistance.

Have a Heineken on me (PayPal OK?)

I might just take more notice of the written word…

Thanks Hans, we are getting closer but now the age difference which is what I needed for calcs is way out. I am getting a difference of the order of 90+ years.

I thought it might be as simple as taking a bigger value from a small value but it wasn’t. The code that calls the TestAge/TestAgeMonths functions is shown below.

=IIf(IsNull([txtDOB])," ",TestAge(([txtTestDate]-[txtDOB])) & "." & TestAgeMonths(([txtTestDate]-[txtDOB])))

In effect I am trying to get the age at which the testing was done (previously I used system date) but the code is not working correctly.

Any suggestions?

Well it is not a Class module so I assume that it is a standard one.

Function TestAge(varTestDate As Variant) As Integer

    Dim varTestAge As Variant

    If IsNull(varTestDate) Then TestAge = 0: Exit Function

'    varTestAge = DateDiff("yyyy", varTestDate, Now)
    
    varTestAge = DateDiff("yyyy", varTestDate, Forms!Students!txtTestAge)
    If Date < DateSerial(Year(Forms!Students!txtTestAge), Month(varTestDate), _
        Day(varTestDate)) Then
        varTestAge = varTestAge - 1
    End If
    
    TestAge = CInt(varTestAge)

End Function

And the form is open and the field is named txtTestDate

Sould I post the db?

Thanks for your quick reply, Hans. What do you mean by a standard module?

I tried changing the reference as suggested but it runs to that line and then states that “..can’t find the field txtTestDate”

This is the reference I used ‘varTestAge = DateDiff(“yyyy”, varTestDate, Forms!Students!txtTestAge)’ when it came up with the message.

The form name is ‘Students’ the field name is ‘txtTestDate’. Have I got the reference correct?