Shorten This Query: If The Cell Doesnt Contain Any Info Dont Update
Apr 9, 2007
i have some code (see below). at the part where it says
If ActiveCell. Offset(0, 9).Value <> "" Then
.Fields("Skill_32") = ActiveCell.Offset(0, 9).Value
i want to make this smaller - i have to write this line bout 60 times 3 times over (incresing the numbers as i go ((0, 9..10..11 etc etc and (Skill_32..33..34 etc etc) )) to give you an idea of what im trying to accomplish - im opening and writing to a database but if the cell doesnt contain any info dont update.
Function EditCPS(ByVal StaffNumber As Long)
On Error Goto Err_Handler
Dim SkillsBuilderDB As Database
Dim RSSkillsBuilder As Recordset
Dim MySQL As String
Set SkillsBuilderDB = OpenDatabase(Worksheets("Adding Data").Range("IV1")) ' database location
MySQL = "select * from CPS where StaffNumber=" & StaffNumber
Set RSSkillsBuilder = SkillsBuilderDB.OpenRecordset(MySQL)
With RSSkillsBuilder
.Edit
If ActiveCell.Offset(0, 9).Value <> "" Then
.Fields("Skill_32") = ActiveCell.Offset(0, 9).Value
Else............................................
Sub unprotectEm() ActiveSheet. Unprotect Password:="shreked" ActiveWorkbook.UpdateLink Name:="R:SHAREDPASSACCEXCELPASSACCCONTROL.XLS" _ , Type:=xlExcelLinks ActiveSheet.Protect Password:="shreked" End Sub
Private Sub Workbook_Open() If ThisWorkbook.ReadOnly = True Then MsgBox "READ ONLY YOUVE BEEN WARNED" End Sub
It works fine until its been opened as "read only". What I mean is I open the file and the read only msg box comes up no prob. When the file avail for editing again I open the file and its not updating the control list.
I have need of VBA code that will check collumn D, starting at cell 7 until cell 206, for when data that is entered starts with 100. When this occurs it will remove all of the data in that same cell except for the last 12 digits.
Here's an example.
When using our barcode scanner to capture a FedEx Tracking number the barcode captures the following:
1001853514360009020200802541997796.
Embedded in that long string of numbers (which are formatted as Text) is the tracking number. Specifically the last 12 numbers are the tracking number. However, FedEx varies this code at times so the last 12 numbers are the tracking number only with the raw data captured by the scanner starts with 100. Until I find additional conditions this VBA code will have to ignore all of data that is captured in collumn D starting at cell 7 until 206.
This is to run stored procedures (in the SQL Database), and pass variables. But...once the query is setup...all I really want to do is change the variable...and "fix" the sql query with the new variable value.
I have some existing code which I did not write, nor do I have the expertise to update. The macro code takes multi rows of data for one person and "flips" the output to be one record per person with columns of data.
The source data looks like this sorted by teacher, Term and Period (columns H and I are the new ones added; the code currently looks through column G only)A
Faculty Name B Periodc C
[Code]......
I just want to add column H and I data to the period course listings by term. So instead of "Term: TM#", just have the display indicate Termcode: Tally/SectSize.... such as (example): "TM1: 20/30"
My Code is as follows:
Option Explicit Dim wsSource As Worksheet Dim wsTarget As Worksheet
Im using a spreadsheet with several third-party add-in queries using Showcase Strategy 4.5. The trouble I'm having is in writing a macro that will automatically refresh the queries. Currently I am using the line
application.run ("refreshdata")
however this refreshes all the queries and at several points I only need to refresh one or two. If anyone has any suggestions then I'd be very gratefull.
Also I feel the need to confess that I pilfered the above code from another forum and so I dont actually know how it works. VBA help doesnt seem to have anything relevant to say and I'd feel better knowing what the code I'm using actually does.
I have a form that laods when the workbook is opened. I also have an MS query that retrieves data from another source. When i open the workbook without the form loading i get a message asking if i want to update the query automatically. When i open the workbook with the form opening the message doesnt show until i close the form meaning i have no data in the sheet to use. Is there a way to get the query to refresh before the form opens?
I am trying to write a creative procedure that on workbook open will check the name of the worksheet if it includes the day's date in sheetname & if it doesn't rename the sheet & delete current region from A1, else exit the sub.
Then runs a query on an Access DB to place in the above worksheet.
I need this due to novice XL & DB (8)users.
This is what I have at the moment on a test DB courtesy of J Walkenbach's sample files .....
I have a Piece of Code that copys a Row in my Spreadsheet and Pastes it underneath the original Row.
The Problem that I am having is that within the Row it Copys there is a Spinner in it. I want the Spinner to be copied also but the Cell that the Spinner is Linked to Says as the Original Cell, it doesnt move Down with the New Row Pasted in.
Here is the code that i have written to Copy the Row and Paste it down a Row.
The first one works fine but the "Description" and "Customer" paste over the top of each other and dont find the empty cell. im sure im missing something!
This submits the following details into the "Stored" table. It also finds an empty cell.
I was done but the formula works great except if the cell is zero or blank I don't want it to include that cell in the average. in this case it still counts.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this WEB 1 http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
it counts data in columns 4 to Endxlright and then creates a graph based on the status. I'm sure there is redundant code in there but can't seem to shorten it myself.
Option Explicit Dim vEndCol, vTotal, nFail, nOth, vRow, vCol, nExc, nSum, n, x Dim Mth As Integer Dim Cell As Range Dim vMth, vstMth, vstart, nOK, xRow, vStatus, wks, Chart
Private Sub Calcs() Sheets("Report").Activate 'Clear previous data and formats Range(Cells(2, 2), Cells(51, 30)).ClearContents Range(Cells(2, 2), Cells(51, 30)).ClearFormats Sheet1.Activate 'Identify last row of data Range("IV8").End(xlToLeft).Select vEndCol = ActiveCell.Column 'variables for all data...................
It worked for a short time but now I am having problems running it. I get an excel error that says excel can't continue. Someone said in a previous post that this could be cleaned up and consolidated. how to clean this up. It took me forever to get this right.
Let's say that in column A I have numbers,"Yes" and "No". I want in column B to have only the numbers from column A, in the same order without any empty ranges, and everytime I add in column A a new number, column B to update automatically with that number. Let's have an example:
A B Yes 12 12 13 No 10 13 No 10 Yes
And if I want to add in column A: A B Yes 12 12 13 No 10 13 25 No 15 10 Yes 25 15
So the column be will update automatically. I already tried =IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROWS(B$1:B1))-ROW($A$1)+1),") but using this many times get's my file very heavy and the excel is working slow.
I'm developing a spreadsheet that is generating relatively simple source code. As far as excel is concerned, based on columns of data in the first sheet, generate other sheets - conceptually straightforward, but the formulas are getting stupid long. For example, suppose I have 4 columns:
Needs A, Needs B, Needs C, and Needs D.
If there is a "Y" in any of these columns, I need to generate corresponding text that is concatenated together:
=if(a1=="Y") use "Field1[if1]" =if(b1=="Y") use "Field2[if2]" =if(c1=="Y") use "Field3[if3]" =if(d1=="Y") use "Field4[if4]"
The result in some other column could be (samples):
- if there is a trailing field, then I need a period separating the two.
Needing this separating period between some of the columns is driving me bat crap crazy. My approach to this point is to build work sheets that feed the main page, thus isolating the logic complexity. This seems to be the only way to segment the processing into something one can edit.
Instead of typing out my code hundreds of times, I would like to replace it with an abbreviation or variable(?). Here is my example. If I have this in the wrong place please let me know. I want to replace Activecell.Interior.Colorindex with ACIC. I tried to Dim ACIC as range, variant, string...to no avail. In combination I Set ACIC = Activecell.Interior.Colorindex and that didn't work. I have been toiling with VBA for a year and I am still trying to learn the terminology and understand it so please forgive me if I seem really NOOBIE. :-)
I'm using the formula '=LEFT(A2,4)+0 to shorten a numbered line of text.Is there a way to adopt that formula to do the same thing to an alphanumeric line of text (i.e. 80A0267AA1 to read 80A0267) or even better substitute a "-" in place of the "A" (i.e. 80A0267AA1 to read 80-0267)? I know this is really picky, but I figure it's worth a shot.
I got a list of counties in Texas, but the list is in mixed case and always has a " County" after each one. I got it converting to upper case, and have tried removing the " County" but no luck. Below is the code I have:
Code: Sub TEXAS() For a = 1 To 254 R = "D" & a i = "a" & a ActiveSheet.Range(R) = UCase(ActiveSheet.Range(i)) Next a End Sub
I have a macro that works but was wondering if there is an easier way to shorten up the part of the code that is very repetitive with an array or ? Nothing I have tried works except the code below.
Code: Sub ClearAndReset() Dim WS1 As Worksheet Set WS1 = ActiveWorkbook.Worksheets("Stocks") WS1.Unprotect Application.EnableEvents = False With WS1
i have a workbook which is mainly use in construction building design... i want users to input ther respective data in that workbook to calculate and design only, but i dont want the user to save the changes they made...
I need to replace format for multiple cells. Most of them have formulas and shows values. But Replace wont let me choose to look in values as you can see in the picture:
I'm completely lost. I just did replace with "Look in: values" option couple of hours ago. And now this... Or am I going crazy?
