My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Find Next Section
|
|
 Mar 4 2012, 12:34 PM
Post
#1
|
|
|
UtterAccess Veteran Posts: 435 From: Kansas  |
I have a process which opens an Excel file via Access. Once the file is open, I have a worksheet that is broken into sections which are exactly the same. I then search the header of each section looking for a specific section name. If it finds the section name,, it will update the information. If it doesn't find the section name, I want it to find the next available section.
I've created the process to find the section name, but the problem is if the section can't be find I'm want to ensure that the processes finds the next proper open section. Here is the coding I've created so far: CT_Name is a text value CODE Open_Section_Name = "NewFA" Set sh4 = wb3.Sheets("SI") With sh4.[A:A] Set c = .Find(What:=CT_Name, LookIn:=xlValues, LookAt:=xlWhole) If Not c Is Nothing Then FARow = c.Row + 1 Else Set c = .Find(What:=Open_Section_Name, LookIn:=xlValues, LookAt:=xlWhole) If Not c Is Nothing Then FArow = c.Row + 1 End If End With Any help is greatly appreciated. Kevin |
 |
 
|
|
Mar 4 2012, 12:53 PM
Post
#2
|
|
|
UtterAccess VIP Posts: 8,140 From: CT |
i get a little confused with the use of .....
If not c is nothing trying this.... which represents if your find cannot find what you are looking CODE IF C is nothing Then do whatever else end if How many values do you need to search for and are they always the same and do you know them in advance? |
|
|
|
|
Mar 4 2012, 01:01 PM
Post
#3
|
|
|
UtterAccess Ruler Posts: 1,090 |
if I may ask, what is a section we are talking here?
|
|
|
|
|
Mar 4 2012, 05:50 PM
Post
#4
|
|
|
UtterAccess Veteran Posts: 435 From: Kansas |
The number of sections (variables) can change whenever this process is run. The initial file is created on Sunday for the following week's information with 14 predetermined sections. Each have the Label, "NewFA". It is updated on a daily basis. The products (sections) could vary from day to day or week to week.
This an example that happens: Sunday information CT1 CT2 Monday information CT2 CT3 When the process is run for Sunday's information, I want the process to create sections CT1 and CT2. Then when the process is run for Monday's information, I want the process to update information in the CT2 section. Because CT3 section hasn't been established yet, it will need to find the next available section. It will then rename the label and update the information for CT3. Kevin |
|
|
|
|
Mar 5 2012, 01:42 AM
Post
#5
|
|
|
UtterAccess Ruler Posts: 1,090 |
CT1, CT2, CT3, etc... are they variable in Excel? or a Named Range/Cell?
|
|
|
|
|
Mar 5 2012, 07:14 AM
Post
#6
|
|
|
UtterAccess Veteran Posts: 435 From: Kansas |
There is a single variable, CT_Name, in a sql string run via MS Access. Those are names of the CTs which need to be recognized so data can be populated.
Kevin |
|
|
|
|
Mar 5 2012, 09:08 AM
Post
#7
|
|
|
UtterAccess Ruler Posts: 1,090 |
If I may ask if you can share us the excel workbook so we can see the structure.
|
|
|
|
|
Mar 5 2012, 09:23 AM
Post
#8
|
|
|
UtterAccess VIP Posts: 8,140 From: CT |
so, after Sunday you have CT1 and CT2, you would change CT2 to CT3 ?? and have CT1 and CT3, then the next time you would want to find CT3 and change it to CT4 ??? all the while only having 2 sections ??
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 24th May 2013 - 08:32 PM |