Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Repeat formulas in a range of cells Results

Good Afternoon,

I can print titles on each page. I can create a dynamic print range. Darned if I can't do both at the same time. I'd prefer to keep VB out of it, but if it comes to that, it comes to that.

Background:
I have a form with 20 rows of entry fields per page. There is a page header. There are row headings which I would like repeated on each additional page without having to copy them for each page - that is, I want the file to display a seamless 120 rows of data entry with just one set of row headings, whereas if the user prints the form, it creates 6 pages of 20 rows, each page equipped with the header information and repeated row headings.

I have created a dynamic print range using the Offset function to encompass all entered data in increments of 20 (so that all sheets display a full grid, even if there is only one entry on that page). If it matters, here is my formula, placed in the Named Range "Print_Area":
=OFFSET(Sheet1!$A$2:$L$2,0,0,24+IF(ISERROR(CEILING(MATCH("*",Sheet1!$A$26:$A$125,-1)/20,1)*20),0,CEILING(MATCH("*",Sheet1!$A$26:$A$125,-1)/20,1)*20))
'Define the start and number of columns of the data - Create one whole page minimum - if there is no extra page data, return "0" - otherwise add 20 rows until print area encompasses all non-empty cells

Problem:
When I set "Print Titles", Excel redefines my dynamic Print Area to a static range of cells instead of my dynamic formula.

Solution:
Beats me!

Any ideas? Let me know if I did not adequately describe my situation. Thanks!

Copy of roster pattern2.xls

Hi,

I am trying to create a roster which includes Fly In Fly Out on a 19 on/9 off, and Residential workers on Mon - Fri. This will be the basis of our Planning, Admin and Training personnel being able to track and arrange our crews for works, payroll submission followups etc.

With regards to the FIFO workforce, when a cell value in the roster area is changed to a certain value (in this case, if would be Flight Day In "FDI" as the value that triggers the fill across) I need for the next 27 cells to populate the roster (*day 1 - Flight day in; then 17 work days to be indicated by the accomodation they are staying at - so i can track how many rooms spare we have at the accomodation; day 19 - Flight day out; then 9 days of R&R). With regards to residential, they only work Mon - Fri, but occasionally there will be weekend work - hope the attached workbook make sense..

A B C D E F G H I.... W X.... AD AE
Name Position Roster Accom Thur 1/1/12 Fri 2/1/12 Sat 3/1/12 Sun 4/1/12

Joe B Supv FIFO Walkabout FDI WA WA WA WA FDO RR RR RR
Amy K Safety FIFO Frogs FR FR FDO RR RR FDI FR FR
Ben H Fitter Resi Resi RR RR

These need to be populated 'automatically' as eventually I will be entering data for over 100 people, and as the Planner, simply do not have enough time! Also, it needs to be manual and set up so that if I enter another "FDI" mid-swing (i.e. they cut their holiday short) then the previous cells are not affected, but the patter repeats moving forward. The same would be for the Residential ("Resi") - that with the occasional weekend work, it does not affect the other weeks/days entries.

The other thing I need to do is to create a formula in the accommodation count cells which not only counts the number of rooms used per facility (countif function used to look up all cells in column which meet the accom ID - i.e. "FR"), but also counts the "FDI" & "FDO" cells. I have the countif working, but cant get the formula right so that it doenst count ALL "FDI"/"FDO", just those which correspond with the accom (and again, only if FIFO).

There will also be a number of manual inputs which would be adhoc (RDO = Rostered Day Off, RR = R&R, AL = Annual Leave, SL = Sick Leave etc.) which I dont want to disrupt the rest the input pattern when updates are entered, and would like to be able to set colours for cells and fonts accordingly (though making it pretty is not essential at present).

I have looked up everything I can and tried numerous things (some of which is still in the VBA editor) and am just stuck - any help would be much appreciated!

Suzan

Hi all,

I am a novice with excel 2010 but need some help with conditional formatting and particularly copy & pasting formulas. Sorry about the per-amble below but it might just make it clearer what I am trying to do. So here goes:

I am trying to put together a training matrix for a construction site so that when I enter a date on a certain cell, another cell changes colour to suit specified date ranges. I doing this by subtracting one date from the other and converting it to a number. This is repeated numerous times for each This number is kept in a particular cell - call the cell P8. There is a range of rows with the identical formula. These other rows relate to other training that may of been taken. So there may be differntt value in, for example, P9, P10, P11, etc. This bit is complete.

Next I have Conditionally Formatted one cell - call the cell D8. The problem I have is when I try to copy & paste this cell to the next D9 it keeps the same formula rather than changing the formula to follow on or be a bit clearer,

cel($D$8) shows on the conditioanl formatting $P$8<1 then turn red. When I go to paste this into $D$9 the same formula appears i.e. in $D$9 $P$8<1 then turn red.

I want this instead to be in $D$9 $P$9<1 and the cell to turn red & so on, & so on.

There will be quite a lot of repetition like this & the only way, so far, I have been able to do this is by manual adjusting each cell.

Any help is greatly appreciated

Hi All,

Ive been painstakingly trying to create a Rota for employees who work offshore.

I think what im looking for is reasonably simple but due the number of criteria im struggling.

This is the kind of output im looking for, based on the following information...

Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday First Mob Platform/site Client Name Rota 01/01/12 02/01/12 03/01/12 04/01/12 05/01/12 06/01/12 07/01/12 08/01/12 09/01/12 10/01/12 11/01/12 12/01/12 13/01/12 14/01/12 15/01/12 16/01/12 17/01/12 18/01/12 19/01/12 20/01/12 21/01/12 22/01/12 23/01/12 24/01/12 01/01/2012 ***** ***** ****** 2+2 1 1 1 1 1 1 1 1 1 1 1 1 1 1

Basically these dates extend to the end of the year but the first column (once its up and running will always be today ( =Today()) with the rest just being a + 1

What im trying to do at the moment is use IF statements something like the following.

Assuming the blue row is row 5 and the first column is column A and this formula is entered in F6

=IF(OR(A6>F5,A6=""),0,IF(and(E6="2+2",F5>=A6,F5,=(A6+13),1,IF(and(E6="2+2",F5>=(A6+28),F5,=(A6+28+13),1,IF(and(E6="2+2",F5>=(A6+56),F5,=(A6+56+13),1,0)))

please forgive the syntax i have just typed this in to this box as im using a separate PC for Excel.

Now You can see my IF's are building up quickly...and that just accounts for the first 3 trips on 1 rota. We have multiple possible rotas, and somewhere in the region of ten trips per year, and really it all has to be
based on dates as opposed to just populating ranges of cells in a repeating sequence...

any pointers would be MUCH appreciated...

thanks

Chris

Hi all,

I don't know why this is happening because I am using Excel 2007, which has many more columns to use than the 2003 version.

I am running a macro which does a series of simple calculations (just summing) and then exports the values in several cells ("sheet1") to a new worksheet ("sheet 2"). I need Excel to repeat this procedure (calculate...copy...paste) 750 times but for some unknown reason the macro always stops/finishes executing when it copies the output to column IV (ie. column 256) in "sheet 2" even though I have asked it to do this copying 750 times. I don't understand why it would be problematic because I am running the 07 version and the macro was also written on the same computer that I am running the code.

The part of the code where this calculating...copying...pasting is as follows:


	VB:
	
Range("AW3").Select 
ActiveCell.Formula = "=sum(RC[-10]:RC[-1])" 
Selection.AutoFill Destination:=Range("AW3:AW16") 
Range("AW3:AW16").Select 
 
Range("AY3:BH16").Calculate 
Worksheets("sheet1").UsedRange.Columns("BI").Calculate 
Range("BI3:BI16").Select 
Selection.Copy 
 
 
Worksheets("sheet2").Activate 
If ActiveSheet.Range("A1") = vbNullString Then 
    Range("A1").Select 
Else 
    ActiveSheet.Range("IV1").End(xlToLeft).Offset(0, 1).Select 
End If 
Selection.PasteSpecial xlPasteValues 
Application.CutCopyMode = False 
 
Worksheets("sheet1").Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Could it be that Excel is set on manual calculations when I run this macro (Application.Calculation = xlCalculationManual); though I seriously doubt this is the cause of it. How could I go about fixing this (maybe with another line of code??)

Thank you in advance for any solutions.

Hi,

I have a macro that is deleting lines and adding new formulas into multiple sheets across a workbook. I am pretty new to VBA. Right now I am just repeating the code over and over for each sheet. Below is a sample of what I'm doing. The actual code is being applied for 18 sheets and adding a calculation ni about 50 cells per sheet. So as you can guess, the code is very long. I was thinking there has to be a way to just do it once across all sheets.

Any ideas?


	VB:
	
Sheets("Template ").Select 
Rows("62:62").EntireRow.Delete 
Rows("66:70").EntireRow.Delete 
Rows("142:151").EntireRow.Delete 
Range("C64").Formula = "=sum(C62:C63)" 
Range("C66").Formula = "=C39+C47+C59+C64" 
Range("C145").Formula = "=sum(C110:C144)" 
Range("C147").Formula = "=C145+C107+C101+C89" 
Range("C149").Formula = "=C147+C66" 
Range("D64").Formula = "=sum(D62:D63)" 
Range("D66").Formula = "=D39+D47+D59+D64" 
Range("D145").Formula = "=sum(D110:D144)" 
Range("D147").Formula = "=D145+D107+D101+D89" 
Range("D149").Formula = "=D147+D66" 
 
Sheets("Template 2").Select 
Rows("62:62").EntireRow.Delete 
Rows("66:70").EntireRow.Delete 
Rows("142:151").EntireRow.Delete 
Range("C64").Formula = "=sum(C62:C63)" 
Range("C66").Formula = "=C39+C47+C59+C64" 
Range("C145").Formula = "=sum(C110:C144)" 
Range("C147").Formula = "=C145+C107+C101+C89" 
Range("C149").Formula = "=C147+C66" 
Range("D64").Formula = "=sum(D62:D63)" 
Range("D66").Formula = "=D39+D47+D59+D64" 
Range("D145").Formula = "=sum(D110:D144)" 
Range("D147").Formula = "=D145+D107+D101+D89" 
Range("D149").Formula = "=D147+D66" 
 
Sheets("Template 3").Select 
Rows("62:62").EntireRow.Delete 
Rows("66:70").EntireRow.Delete 
Rows("142:151").EntireRow.Delete 
Range("C64").Formula = "=sum(C62:C63)" 
Range("C66").Formula = "=C39+C47+C59+C64" 
Range("C145").Formula = "=sum(C110:C144)" 
Range("C147").Formula = "=C145+C107+C101+C89" 
Range("C149").Formula = "=C147+C66" 
Range("D64").Formula = "=sum(D62:D63)" 
Range("D66").Formula = "=D39+D47+D59+D64" 
Range("D145").Formula = "=sum(D110:D144)" 
Range("D147").Formula = "=D145+D107+D101+D89" 
Range("D149").Formula = "=D147+D66" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thanks in advance.

I am currently using the Worksheet_Change function to hide rows on another worksheet if the target value is"0". I am a complete novice but have pieced this together using a pair of IF's for each target, but there are quite a few rows and if/when I insert a row it breaks the Target to Row relationship. I'd like to be able to configure ranges to consolidate it into a pair (or even a single IF).

Or maybe there is a better way of achieving what I a m trying to do...

. The pattern is deterministic:

value of sheet1:C10 hides sheet2:Row18
value of sheet1:C11 hides sheet2:Row19
value of sheet1:C12 hides sheet2:Row20

etc...

=======================================

	VB:
	
 Range) 
     
    If Target.Address = "$C$10" Then 
        If Range("C10").Value = 0 Then 
            Sheets("Rental Quote").Rows("18:18").Hidden = True 'Re-hide all rows
        End If 
        If Range("C10").Value > 0 Then 
            Sheets("Rental Quote").Rows("18:18").Hidden = False 'Re-hide all rows
        End If 
    End If 
     
    If Target.Address = "$C$11" Then 
        If Range("C11").Value = 0 Then 
            Sheets("Rental Quote").Rows("19:19").Hidden = True 'Re-hide all rows
        End If 
        If Range("C11").Value > 0 Then 
            Sheets("Rental Quote").Rows("19:19").Hidden = False 'Re-hide all rows
        End If 
    End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Repeated through C30

=======================================

For bonus points: One of the target cells contains a formula. However even if the result of the formula is "0" the row is never hidden... How do I get the function to look at the formula result?

Hi,
I'm new to the forum and inexperienced in VBA. My problem is that I work on a file with around 20000 rows and average, stdev and rank functions have to be applied to individual ranges which are on average 12 rows and separated by a blank cell.
It is extremely time consuming to apply the functions to each range individually so I was wondering if there is a way to mark the entire columns of data which need the functions applied but have the functions apply to each range individually. In other words the function recognizes that the blank cell indicates the end of one range, does the calculation, and repeats for the next range and so on.
Any suggestions on solving this problem would be gratefully appreciated.
Chris

Hi, I'm trying to Copy and Insert formulated cells from sheet "Add Entry" range A3:AZ4 to Sheet "January" just above the last row containing data (the Totals line) starting in column B. The cells need to remain the same in cell size and maintain the formulas that are inmbedded within them. I want to be able to do this repeatedly by applying this macro to a button. I would also like (if possible) to alternate the color of these cells in an alternating row fashion. Thank you so much for your help. Chad

My search resulted in WAY to many matches, After a few hrs of readin- im no closer to an answer.

Inspired by Shg, I decided to try my hand at a few basic macros.

One of my macros is a tied to a worksheet_change. The macro runs and gives me the result that i want, however excel flips to the sheet that the sub is running for.

example:

Sheet1, Change the cell
excel flips the visible to sheet2
macro runs

Its kinda of annoying. I would like excel to stay on sheet1 where i am trying to finish data entry. I suspect it is how i have writen the macro. Any help at all on how to clean it up.. or a better way to write the same thing would be very appreciated.


	VB:
	
 TermPlacement() 
     
     ' Term_Placement Macro
     ' Uses the Value of Term to insert a formula repeatedly in a colum.
     
    Dim sNFormula As String 
    Dim rTerm As Range 
    Dim rStart_Cell As Range 
    Dim rNpayment As Range 
     
     'The reference cell located directly above the first cell in column
    Set rStart_Cell = Worksheets("Floor Plan").Range("$B$13") 
     'Additional set values
    Set rNpayment = Range("Npayment") 
    Set rTerm = Range("Term") 
     
    sNFormula = "=IF(OFFSET($B$13,($D$6-1),6)

Hi,

I posted my initial question about sorting merged cells here (http://www.ozgrid.com/forum/showthread.php?t=149511) and had an additional question about the sort which in turn brought up further issues. Maybe I'm going about this all wrong but I really hate to go all the way back to "start".

I'm using Excel 2003 in Windows XP SB3 environment.

Firstly, thank you a million times to shg and PCI for their time and effort spent in crafting what looked like an excellent response to my question. I have a workbook that is going to be used to record man days for a variety of projects. Each superintendent will be responsible for their own project record. The issue was how to sort rows with merged cells - please refer to the attached document - so that they are in numeric order.

I started with some code written by Aaron Blood in 2005 and shg updated it to fit my requirements. However I didn't create the necessary format tab that the code called for before I submitted the workbook. I created it after the fact and based it on Aaron Blood example. Problem came about when I was testing it and noticed that it still wasn't exactly right. It should sort by the job number and if there were blank cells, leave them blank at the end of the report. However, for some reason, it either repeated the job number and job name or it would put "Job Number" and "Job Name" into ALL the cells in column A & B.

Now after going carefully through the steps I noticed another flaw in my thinking. I was focused on 1 quarter only. I need to be able to have data flow from 1 quarter to the next if the jobs are active and then total on the "Totals 2011" tab. I can give my guys a quarter at a time and manually put the information on the "Totals 2011" tab or, if it's easier, try to automate it.

I am open for suggestions from the experts. When I got into this project it was easy enough to accomplish at my level. With the other added expectations, it is way beyond me. Is there a way to alter the existing code (see below) to do what we need? I am going to be taking a VBA class next semester but I need this as soon as possible.


	VB:
	
 
Sub Sorted_Report() 
    Dim r           As Range 
    Dim i           As Long 
     
    Set r = Intersect(Range("A3").CurrentRegion, Range("3:" & Rows.Count)) 
     
    With r 
        .UnMerge 
         
        On Error Resume Next 
        .Resize(.Rows.Count, 1).Offset(, 0).SpecialCells(xlBlanks).Formula = "=R[-1]C" 
        .Resize(.Rows.Count, 1).Offset(, 1).SpecialCells(xlBlanks).Formula = "=R[-1]C" 
        On Error Goto 0 
        .Columns("A:B").Value = .Columns("A:B").Value 
         
        .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, DataOption1:=xlSortNormal, _ 
        Key2:=.Cells(1, 2), Order2:=xlAscending, DataOption2:=xlSortNormal, _ 
        Header:=xlNo 
        Range("VBA_RecFmt").Copy 
        .PasteSpecial Paste:=xlPasteFormats 
         
        Application.DisplayAlerts = False 
         
        For i = 1 To r.Rows.Count Step 3 
            r(i, 1).Resize(3).Merge 
            r(i, 2).Resize(3).Merge 
        Next i 
         
        Application.DisplayAlerts = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you again for any and all help! I hope to be able to do this myself soon and will pay this forward when I am able.

~ Brenda ~

Hi all,

I'm looking for a solution to a problem and from what I've read in the forums so far, this is the place to come. I first want to say that I have tried to solve the problem by using guidance from other similar threads and adapt those answers to my needs, but as my understanding of VBA is non-existent this was somewhat difficult. I don't want to post a repeat question and I hate wasting people's time if unnecessary, but I can't do this on my own.

That being said, here is the problem;

(I have attached a simplified copy of the worksheet I am working on for reference)

I would like to lock a particular cell in my spreadsheet based upon the selections made from two drop down validation lists (Cells “C4” & “D4”)

Essentially, if a correct choice is made in both, then the cell “E4” becomes unlocked for editing. If however, the correct selection is made in one list, but not the other, I need the cell “E4” to remain locked and a message to appear to prompt the user to correct their mistake.

Furthermore, both the validation lists have a whole range of selections that are very similar but have subtle differences;

E.g.

"Soft Eye by Talurit"
"Soft Eye by Splice"

Is it possible to write a VBA code that performs a similar function to the =IF(ISNUMBER(SEARCH("**",))) formula, so that if any choice that contains the word "Soft" is selected from either or both lists then the cell “E4” becomes unlocked, but if the selection does not contain that word then the cell remains locked?

If anyone could provide a code to make this happen, I would be very grateful! But please explain how to do this, as the word “Soft” won’t be the only parameter and I may need to alter the code so I need to understand the principle behind it; For example, on the spreadsheet, if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Soft Eye’ is selected then cell E4 is unlocked for editing. If in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Fused and Tapered’ is selected then cell E4 is unlocked for editing, but if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Standard Eye’ is selected then cell E4 remains locked and an error message appears to alert the user that his/her choices are incorrect. If however, "Soft" is not selected in either list then cell E4 remains locked (it has to be selected in at least one of the two lists to allow editing).

This is a work in progress (not all the formulas that will eventually be used have been entered yet, so please excuse the untidiness of it all) and the choices are likely to change based on input from my colleagues, so I need to understand how the VBA works in order to alter it accordingly, but I don’t want to put any more work into this (it’s still in the early stages) if it isn’t possible to achieve these needs.

Furthermore, if it is possible to incorporate an error message, please could you tell me how to alter what it says, as this too has not yet been decided upon?

I hope I’ve phrased this clearly enough, but please let me know if any clarification is needed.

Thanks for taking the time to read this! I hope someone can help…

Hi All,

I have a sheet which has a fixed number of rows (conditional formatting applied), number is 150.

The code below allows key users to insert row/rows in between existing data, when required. therefore the sheet length grows.

After insert I want to be able to position cursor on 1st empty row ( code works), then delete entire row, and repeat this automatically for the same number of rows that were inserted.

Any help gratefully recieved.

thanks

isca


	VB:
	
 
Sub InsertNumRows() 
     
     'prevents macro being run in wrong sheet and meesing up data
    If ActiveSheet.Name  Sheet1.Name Then 
        MsgBox "YOU ARE NOT IN THE CURRENT RMAs SHEET" 
        Exit Sub 
    Else 
         
         
         ' SECTION 1 restrict macro users to those with password
        Dim Password As String 
        Password = InputBox("Please enter password below", "Password", "??????") 
         
        If Password  "insert" Then 
            MsgBox "Incorrect Password" 
            Exit Sub 
        Else 
             ' SECTION 2 reminds user to have all other users out of spreadsheet
            Dim Response As Integer 
             
            Response = MsgBox(prompt:="Are All Other Users Out Of Spreadsheet?", Buttons:=vbYesNo) 
            If Response = vbNo Then 
                MsgBox " You Cannot Insert Rows Until All Other Users Out- Click on Tools/Sharedworks to check" 
                Exit Sub 
            Else 
                 ' SECTION 3 reminds user to ensure active cell is in correct row
                Response = MsgBox(prompt:="Is Cursor On Row below Which You Want Rows To be Inserted?", Buttons:=vbYesNo) 
                If Response = vbNo Then 
                    Exit Sub 
                Else 
                     ' SECTION 4 how many rows are required on insert
                    Dim rng, n As Long, k As Long 
                     'Dim rng1 As Range
                    Application.ScreenUpdating = False 
                    rng = InputBox("Enter Number Of Rows Required.") 
                    If rng = "" Then 
                        MsgBox " Incorrect Range Data Entered- Macro will Exit" 
                        Exit Sub 
                    Else 
                         ' SECTION 5 insert rows and copies formula and formats
                        Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(rng, 0)).Select 
                        Selection.EntireRow.Insert 
                         'need To know how many  formulas To copy down.
                         'Assumesfrom A over To last entry In row.
                        k = ActiveCell.Offset(-1, 0).Row 
                        n = Cells(k, 256).End(xlToLeft).Column 
                        Range(Cells(k, 1), Cells(k + Val(rng), n)).FillDown 
                         'next line clears copied data just leaving formulas
                        Selection.Offset(0, 0).EntireRow.SpecialCells(xlConstants).ClearContents 
                         
                         
                    End If 
                End If 
            End If 
        End If 
    End If 
     
     ' SECTION 5 DELETES A BLANK ROW TO KEEP SHEET AT 150 ROWS
     ' SETS CURSOR TO CORRECT INSERT POINT FOR NEW BLANK ROWS
     
    ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2).Select 
     'Application.Goto Range("A" & ActiveCell.Row), True
    Application.ScreenUpdating = False 
     'MsgBox "LOOK TO SEE WHERE THE CURSOR IS"
     
     'NEED CODE TO MAKE "ENTIREROW.DELETE" REAPEAT AS MANY TIMES AS THE
     ' NUMBER OF ROWS INSERTED IN SECTION 4
     
    ActiveCell.EntireRow.Delete 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to return a cell address range based on values in different columns. Attached is an example.

In Column A1 I have "Range" and below that a set of numbers from 1 to 31.

In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.

What I want to do is as follows:-

I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.

I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.

Any assistance with this will be greatly appreciated

Many Thanks

Dave

Good morning,

Hope someone can help me out.

I am trying to insert values into a access table.

The spreadsheet where I am taking values from does contain blank cells. This is throwing out my code to insert the values into my table.

This is my code.

	VB:
	
 ADODB.Connection 
Dim b As Long 
Dim dtmWeekEndingDate As Date 
Dim strPayRollNumber As String, strRateType As String 
Dim strUnits As Double, curTotalCharge As Currency 
Dim stDB As String, stConn As String, stSQL As String 
Dim strINSERT As String, strWHERE As String, strSQL As String 
 
Dim Period As String, BN As String, TS As String, Name As String, TotalHours As Double 
Dim Branch As String, CostCode As String, Pay1 As Currency, Hours1 As Double 
Dim Pay2 As Currency, Hours2 As Double, Pay3 As Currency, Hours3 As Double 
Dim TotalPay As Currency, NI As Currency, TotalPayNI As Currency, WTR As Currency 
Dim MarkUp As Currency, Expenses As Currency, MgtFee As Currency, TotalNET As Currency 
Dim SuppliersVAT As Currency, MGTFeeVAT As Currency, TotalVAT As Currency 
Dim Agency As String, JobTitle As String, WeekEnding As Date, VATMgtFee As Currency 
Dim ReportingTo As String, TotalInvoice As Currency 
Dim ans As String 
 
 
 
 
 
ans = MsgBox("Are you sure you want to add updates into CTRDD?" & vbCrLf & "Changes can not be reversed", vbYesNo) 
If ans = vbNo Then 
    Exit Sub 
End If 
 
 
 
Application.Cursor = xlWait 
Application.DisplayStatusBar = True 
Application.StatusBar = "Inserting Records....." 
 
 
stDB = "G:CTRDDDbCTRDD_be.mdb" 
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=" & stDB & ";" 
 ' open the database
 
Set cnt = New ADODB.Connection 
 
 
b = 2 ' the start row in the worksheet
 
 
Do While Len(Range("A" & b).Formula) > 0 
     ' repeat until first empty cell in column C
     '  MsgBox (Cells(b, No1Column).Value)
     
     
    Period = Range("A" & b).Value 
    BN = Range("b" & b).Value 
    Branch = Range("c" & b).Value 
    TS = Range("D" & b).Value 
    Name = Nz(Range("E" & b).Value, 0) 
    TotalHours = Range("F" & b).Value 
    CostCode = Range("G" & b).Value 
    Pay1 = Range("H" & b).Value 
    Hours1 = Range("I" & b).Value 
    Pay2 = Range("J" & b).Value 
    Hours2 = Range("K" & b).Value 
    Pay3 = Range("L" & b).Value 
    Hours3 = Range("M" & b).Value 
    TotalPay = Range("N" & b).Value 
    NI = Range("O" & b).Value 
    TotalPayNI = Range("P" & b).Value 
    WTR = Range("Q" & b).Value 
    MgtFee = Range("U" & b).Value 
    TotalNET = Range("W" & b).Value 
    SuppliersVAT = Range("X" & b).Value 
    Agency = Range("AD" & b).Value 
    JobTitle = Range("AE" & b).Value 
    TotalInvoice = Range("AC" & b).Value 
     
    WeekEnding = Range("AF" & b).Value 
    ReportingTo = Range("AG" & b).Value 
     
     
     
     
     
     
    strINSERT = "INSERT INTO tblCGMLAnalyser([Period],[BookingNumber],[Adecco Branch],[T/Sheet No],[Name],[Total Hours],[Cost
Code],[Pay Rate 1],[Hours 1],[Pay Rate 2],[Hours 2],[Pay Rate 3],[Hours 3],[Total Pay],[NI],[WTR],[Mngt Fee],[Net
Invoice],[Suppliers VAT],[VAT on Management Fee],[Total VAT],[Field25],[Agency],[Job Title],[Week Ending],[Reporting To:])" 
    strINSERT = strINSERT & " VALUES ( '" & Period & "','" & BN & "'," & Chr$(34) & Branch & Chr$(34) & ",'" & TS & "'," &
Chr$(34) & Name & Chr$(34) & ",'" & TotalHours & "','" & CostCode & "','" & Pay1 & "','" & Hours1 & "','" & Pay2 & "','" &
Hours2 & "','" & Pay3 & "','" & Hours3 & "','" & TotalPay & "','" & NI & "','" & WTR & "','" & MgtFee & "','" & TotalNET &
"','" & SuppliersVAT & "','" & VATMgtFee & "','" & TotalVAT & "','" & TotalInvoice & "'," & Chr$(34) & Agency & Chr$(34) &
",'" & JobTitle & "','" & WeekEnding & "'," & Chr$(34) & ReportingTo & Chr$(34) & ")" 
    strSQL = strINSERT 
    Debug.Print b & ". " & strSQL 
     
     
    cnt.Open stConn 'Open connection.
    cnt.CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
    cnt.Execute (strSQL) 
    cnt.Close 
    b = b + 1 ' next row
Loop 
 
 
Set cnt = Nothing 
 
 
Application.Cursor = xlDefault 
Application.StatusBar = "Ready" 
MsgBox ("CGML Updates have now been added") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What do I need to do to enable inserting of null values?

I have tried using Nz() but doesn't seem to work

	VB:
	
Name = Nz(Range("E" & b).Value, 0) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Thanks for your help!

Please have a look on the attached excel file. I have data in columns A to G with headings field 1 to field 6. Using autofilter rmethod to arrange the data from column N with the following code. First I put autofilter on field 3 with criterial -1800 and then put on field 6 with criterial 0 to 15. Repeat then with field 3 = -1400. This works well for small number of data. But, in my real application, I have 30 different criterial in field 3 and the data in column A to G is occupying up to rows 50000. So, this autofilter method is very slow. Is there a way to implement this with formulas in column N to AC so that it will be much faster.

Appreciate your help.


	VB:
	
 arranage() 
    Dim i As Integer, n As Integer, mea(2) As String 
    mea(1) = "-1800" 
    mea(2) = "-1400" 
    Range(Cells(3, 1), Cells(3, 7)).AutoFilter 
    For n = 1 To 2 
        Selection.AutoFilter Field:=3, Criteria1:=mea(n) 
        For i = 0 To 15 
            Selection.AutoFilter Field:=6, Criteria1:=Format(i) 
            Cells(4, 7).Select 
            Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Copy Destination:=Cells(4 + (n - 1) * 8,
14 + i) 
        Next i 
    Next n 
    Selection.AutoFilter 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I get very frustrated by not being able to add in a single cell value to apply to all individual x-axis values within a cell range in the "source data" option in charts. In particular I am looking to add 3 lines onto a line graph (one for the mean of a series and then 2 sd tolerance band). I do not want to have to copy the single value into a cell range repeatedly, and would prefer a formula to achieve this. Any ideas?

Hi,

I'm trying to figure out how have the below code re-run through a certain array of worksheets in the same workbook.

From the following code I need to loop through an array of worksheet names performing the same vba code each time:


	VB:
	
Sheets(Array("Sheet1", "Sheet2")).Select 
 
For Each worksheet In Array 
     
Next worksheet In an array 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can anybody help me out with this one please?

Here is the full code:


	VB:
	
 test() 
    Dim cmonth As Date, cfind As Range, pmonth As Date, nmonth As Date, ws As Worksheet 
    With Worksheets("Dashboard") 
        cmonth = .Range("J2").Value 
        pmonth = .Range("K2").Value 
        nmonth = .Range("I2").Value 
         
        For Each worksheet In a Set array 
             
            ActiveSheet.Cells.EntireColumn.Hidden = False 
             
            Set cfind = .Rows("7:7").Find(what:=CDate(cmonth), lookat:=xlWhole) 
            If Not cfind Is Nothing Then 
                If cfind.Offset(2, 0).Value  "" Then 
                    Range(cfind.Offset(0, 1), cfind.End(xlToRight)).EntireColumn.Hidden = True 
                    MsgBox "This action can't be performed as you will over write the formulas. Please insert the correct
current month and previous month in the Dashboard sheet" 
                    Exit Sub 
                Else 
                End If 
            End If 
             
            Set cfind = .Rows("7:7").Find(what:=CDate(pmonth), lookat:=xlWhole) 
            If Not cfind Is Nothing Then 
                Range(cfind.Offset(1, 0), cfind.End(xlDown)).Copy 
                Range(cfind.Offset(1, 1), cfind.Offset(1, 1).End(xlDown)).PasteSpecial xlPasteAll 
                cfind.Offset(1, 0).PasteSpecial xlPasteValues 
            End If 
             
            Range(cfind.Offset(0, 2), cfind.End(xlToRight)).EntireColumn.Hidden = True 
        Next worksheet In an array 
    End With 
     
    Application.CutCopyMode = False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Kind regards,

05125668

Greetings,

Please do not tell me to use headers. I MUST use a function within a cell.

Why? ..because my customer has a complex header using the "repeat rows at top of page". These so-called header rows have formulas and functions and can not be re-created in the "Page Setup > Custom Header" field. They do not want the page number above their header, or at the bottom of the page. The want me to display the (page) and (pages) within the repeated rows.

Note: The same rows repeat at the top of every page, so the same cell that displays the page number info needs to keep track of how many times it has been displayed. I thought that maybe a global variable (counter) could be used.

Or, how about a custom Print function that continuously updates a global page number variable as it iterates through the pages using set print range - effectively printing one page at a time.

I know VBA, so solutions involving VBA are fine.

Thanks in advance,
David

Hi All
I'm a novice trying to write my own code.... sorry
At the moment the code is only working on the active worksheet and also tries to repeat itself in the active worksheet too??
Please help - what am I doing wrong?
Thanks Very Much

FYI
The code cannot nominate specific worksheet names as the number and names will vary.

The number of rows in each worksheet will differ - so I'm finding a string of text that is constant on all worksheets and selected rows from that point down.

The code goes through all worksheets in the active workbook and formats them to reset some values to zero, remove specific calculation sections, convert all formulas to values and delete unwanted rows and columns:


	VB:
	
 Removecosts() 
    Dim sh As Worksheet 
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row 
     
    For Each sh In ActiveWorkbook.Worksheets 
        Range("BN1").Select 
        ActiveCell.FormulaR1C1 = "N" 'activates formulas to show zero values
         
        With Range("BJ16:BJ" & lastrow) 
            Set c = .Find("Original Budget", LookIn:=xlValues) 
            If Not c Is Nothing Then 
                firstAddress = c.Address 
                Do 
                    Range(c.Address).EntireRow.Select 'find a specific rows to the last row and clear formulas
                    Selection.Resize(lastrow, 200).Select 
                    Selection.ClearContents 
                Loop While Not c Is Nothing And c.Address  firstAddress 
            End If 
        End With 
         
        Cells.Select 
        Selection.Copy 'convert all formulas to values
        Range("A1").Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
        Application.CutCopyMode = False 
         
        With Range("BI16:BI" & lastrow) 
            Set c = .Find("TOTAL COST", LookIn:=xlValues) 
            If Not c Is Nothing Then 
                firstAddress = c.Address 
                Do 
                    Range(c.Address).EntireRow.Select 
                    Selection.Offset(11, 0).Select 
                    Selection.Resize(lastrow, 200).Select 'Delete empty rows once formulas become values
                    Selection.Delete 
                Loop While Not c Is Nothing And c.Address  firstAddress 
            End If 
        End With 
        Columns("A:B").Delete 'delete unwanted columns
        Columns("BJ:DD").Delete 
        Range("A2").Select 
        Application.CutCopyMode = False 
    Next sh 
     
    Sheets("Overview").Select 
    Range("A1").Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.