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............................................

View 7 Replies


ADVERTISEMENT

My Links Dont Update All The Time

Jun 6, 2006

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.

View 2 Replies View Related

Shorten Cell's Content When Specific Conditions Are Met?

Feb 1, 2013

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.

View 3 Replies View Related

Update SQL Query Rather Than ADD

Mar 2, 2010

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.

Is there a better way than: ....

View 9 Replies View Related

Update Existing Code To Include Two More Fields Of Info

Jun 6, 2014

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

[Code].....

View 1 Replies View Related

Update Query Table Using VBA

Jan 7, 2013

I'm trying to update a query table in excel, but it keeps giving me the error 1004

Worksheets("Data-All").Range("A4").QueryTable.Refresh BackgroundQuery:=False

Cell a4 is in my query table.

View 2 Replies View Related

Update Individual Query

Nov 21, 2006

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.

View 3 Replies View Related

Update Query Automatically

Dec 14, 2006

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?

View 2 Replies View Related

DB Query Refresh/Update Macro

Jun 16, 2008

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 .....

View 9 Replies View Related

Update Web Query Every X & Time Stamp

Aug 15, 2007

I've got a web query setup to poll a table detailing traffic information from my state's DOT page.

I have the web query set to update every 5 min.

I would like to save a particular cell from this query every 5 min and place a timestamp next to it.

Ultimately, I would like to build a table, one column = time, the other column = traffic data and have each row be 5 min apart.

View 9 Replies View Related

Select First Empty Cell... Doesnt Work

Aug 10, 2009

I am using this code to select the first empty cell in column A.

View 9 Replies View Related

Update Screen After Data Query Refresh

Dec 18, 2006

I am bringing data in from an ODBC database, I am using ActiveSheet.QueryTables(1) and then .refresh.

I want to run a sub after the data has been brought in which finds the last row and adds a formula.

The problem I have is that the data brought in is not show on the screen until all of the macros are finished.

Is there a way of forcing the screen to update?, I have tried the recalculate and refreshall commands or is there another method I should be using?

Sub autofill()

Dim myobj As Object

If ActiveSheet.QueryTables.Count > 0 Then
Call clearscreen
Application.DisplayAlerts = False

Set myobj = ActiveSheet.QueryTables(1)

I require the data to be on the screen before I can find the last row used which is done in "addmaths"?

View 5 Replies View Related

Spinner Link Cell Doesnt Change When I Copy And Paste The Row

Jan 24, 2010

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.

View 5 Replies View Related

Dont Find The Empty Cell In Vba

Oct 27, 2008

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.

View 5 Replies View Related

Dont Include In The Average If Cell Is Zero Or Blank

May 28, 2009

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.

=IF(SUM(H32>0,H67>0,H102>0,H137>0)=0,0,SUM(H32,H67,H102,H137)/SUM(H32>0,H67>0,H102>0,H137>0))

View 3 Replies View Related

Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

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

Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

View 2 Replies View Related

To Shorten An If Formula

Jul 28, 2008

My formula is:

=IF(E4="Break",D4,IF(H4="Break",G4,IF(K4="Break",J4,IF(N4="Break",M4,IF(Q4="Break",P4,0)))))

My problem is that excel only lets me use 7 IF functions and I need a total of 12. So I need a different way to perform that same function.

View 13 Replies View Related

Shorten Vba Code

Jun 26, 2007

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...................

View 5 Replies View Related

Shorten Code: Clean This Up?

Mar 30, 2007

I am trying to run the following

ChDir _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor"
Workbooks.Open Filename:= _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor2 Tuesday.xls" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor3 Wednesday.xls" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor4 Thursday.xls" _
, UpdateLinks:=0
Workbooks.Open Filename:= _
"C:Documents and SettingsChrisDesktopRainforestMASTER LABORNew Master Labor5 Friday.xls" _
, UpdateLinks:=0...................

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.

View 6 Replies View Related

Automatically Update Only With Numbers (ifnumber-update And Go To Next Cell)

Oct 18, 2013

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.

View 13 Replies View Related

How To Shorten Conditional String Construction

Feb 10, 2014

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):

Field1[if1]
Field1[if1].Field2[if2]
Field3[if3]
Field2[if2].Field4[if4]

a couple of notes:

- you can have field 2 without field 1, etc.

- 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.

View 3 Replies View Related

Shorten Code Typing Methods

Aug 11, 2007

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. :-)

View 9 Replies View Related

Formula To Shorten An Alphanumeric Line Of Text

Jan 25, 2010

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.

View 10 Replies View Related

Shorten String And Convert To Upper Case?

Mar 7, 2013

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

View 7 Replies View Related

Fill Info In Sheet One And Have Same Info Appear In All Sheets That Follow

May 12, 2014

I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?

View 3 Replies View Related

How To Fill Vertical Columns With Info From Horizontal Info

Aug 22, 2014

I have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g

The info in these columns at present has been manually entered but I am sure it could be automated.

OOL Roster Final 18-31Aug14.xlsx

View 1 Replies View Related

Easier Way To Shorten Up Part Of Code That Is Repetitive With Array

Jun 3, 2013

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

[code]....

View 3 Replies View Related

If Else Statement Doesnt Work Well...

Mar 25, 2009

If Else Statement doesnt work well. I just create a code like:

View 2 Replies View Related

Workbook Doesnt Allow Re-saving

Dec 14, 2009

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...

View 9 Replies View Related

Replace Doesnt Look In Values

Aug 8, 2006

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?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved