Is there a way to prevent a workbook from closing or being submitted until information has been entered into the following cells? B78, B80, B82, B84, B86, B88, B90, B92, B94, B96, B98, B100, B102, B104, and B106?
View 8 RepliesThe application reads in a file, whcih can have various formats. To check which format it's in, I plan to look for certain empty fields/ cells. I can successfully detect a group of empty cells by explicitly testing each one, but when I put them all in a range and test that, the check fails. So far I've reduced the problem to the following example code.
Sub check_clear()
Range("g1:g8").clear
If IsEmpty(Range("a1")) Then
Range("g2") = "A1 empty"
End If
If IsEmpty(Range("b1")) Then
Range("g3") = "B1 empty"
End If
If IsEmpty(Range("c1")) Then
Range("g4") = "C1 empty"
End If
If IsEmpty(Range("d1")) Then
Range("g5") = "D1 empty"
End If..............
The result is that each individual cell check results in the relevant "XX empty" message. However, the test that the range of multiple cells is empty never produces a result. I'd really like to understand the underlying reason - as well as find out how to perform an isEmpty test on a range. I'm looking more for guidance and insight than a canned solution
write this in VBA on the Workbook Level, "ThisWorkbook" : IF range (B20:B53, E20:E53, H20:H51) are all filled through user keypunched values--checks and coupon amounts.AND range (C20:C52, F20:F52, I20:I50) are equal to = " "
-- these ranges contain formula that spits out values only when there are discrepancies with the manually keypunched values above' otherwise it's equal to " ".THEN call batch02. batch02 is a macro that prints the specified batch.I have attached the filed I am working with. There are 25 batches, hopefully I can replicate the codes by just changing the ranges and the print macro.
I'm trying to select a contiguous range of cells in a column and then iterate over that. I keep getting error "91", object variable not set. I have:
View 5 Replies View RelatedI want to be able to copy cell D4, I4, G10, G12 etc from one spreadsheet and paste into another spreadsheet in the same locations as the source.
Is there a macro that will do this?
I have a combobox on a spreadsheet it is populated on a sheet called teams :
The Format control has an input range of: Teams!$B:$B
Now this has a few thousand empty cells in the range but I would need it to only populate with the cells that are not empty.
I recently found this code for selecting a whole column of non continuous cells.
ActiveSheet.Range("a1",ActiveSheet.Range("a65536").End(xlUp)).Select
How can I change the "a1" & "a65536" so it can work and be activecell instead?
I am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.
I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.
If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.
I'm stuck using Excel 2003 to auto-populate a cell.
I have a range of dates in five consecutive columns called:
Phase 1, Phase 2, Phase 3, Phase 4 and Phase 5
I enter the date that 'Phase 1' starts under the first header. Once Phase 2 starts I enter a date under 'Phase 2', and so on to Phase 5.
Each phase is consecutive to the next so will always be filled in from 1 to 5.
I want to create an additional column called 'Status' that shows the Column Title of the last phase with a date in it. For example, if Phase 1 to 3 had dates but 4 & 5 were blank, "Phase 3" would be displayed in the 'Status' column.
I've tried nesting some ISBLANK functions without any luck.
I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
I have a range of cells which contains one of two text values (Ok and Not Ok). I would like to check my range of cells for these values. If this range have one cell that contains the text "Not Ok" I would like the formula to say "Not OK". If all cells contain "Ok" I would like the formula to say "Ok".
View 8 Replies View RelatedThis does not seem to be working and I do not know why.
If Range("T3:T999").Value = "Duplicate" Then
MsgBox "It did work"
Else
MsgBox "What happened??"
End If
Upon deactivation of a worksheet I am trying to check a range of cells("B8:M8") for empties. If any cells are empty I want to generate a MsgBox that asks the user to remedy the situation. It is not necessary to tell them which cell is empty but it would be nice. Can anyone help me format this code?
View 2 Replies View RelatedI am using the following code to check data is entered in certain cells...
View 2 Replies View Relateddetecting if a range has colored cells. What would the correct syntax be for the line in red?
Sub CheckForDups()
Dim ColumnNumber As Integer
ColumnNumber = 1
Columns(ColumnNumber).Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions (Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
[code]....
I have an if statement as follows:
If IsEmpty(Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol)))
Then
i did a select to make sure it was selecting the whole range I want and it works fine:
Range(Cells(iCurrentRow, iFirstDataColumn), Cells(iCurrentRow, iTotalCol)).Select
Inside my range I can have cells with 0s in them and cells with nothing in them. What I would like my if statement to do is return true ONLY when ALL cells have nothing in them. At the moment, even if I have 0's in some cells, it's returning false.
If I have the following cells and values
A1 = 0
A2 = 0
A3 = ""
A4 = 0
I want a vba if statement that makes A5 = "Numeric.
If I have:
A1 = 0
A2 = 0
A3 = A
A4 = 0
I want A5 to = "Non Numeric"
Similarly if:
A1 = ""
A2 = ""
A3 = ""
A4 = ""
I want a5 to = "Non Numeric"
Is there a way to do this without looping through each cell in the range?
I have X amount of cells (formulasheet) , these are used to input data, which is then summarized into a sheet on the same file. I'm trying to find a code that forces the user to fill out all the fields. If the user do not, the "submit button" will not proceed, at least not work. Almost like a forum registrating, where it is required to fill it all some mandatory fields in order to proceed.
The cells datatypes are mixed, some are Integer and some are String.
Is there anyway that are code can be written "if the cell(s) is FALSE (nothing in the cell) then display message.
im having a little trouble writing a macro to :
check if a7:t7 is empty, if so delete a6:t7 and change cell colour of a6:t7 to 'no fill'
I am looking for a macro that will check 2 things....then do something.
The first is if a range of cell values in are blank. lets say
Sheets("Sheet1").Range("D6:G48").
Next is if a particular cell contains a given value ,
Sheets("sheet1").Range("E5") should equal "Year 1" if true.
If both these are true then I want the macro to copy a range of cells from
Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D6:D48").
I tried to use IsEmpty but it seems to return a "False" if a range of cells
are being tested.
How to check if a range of cells matches specific text.
Example:
A1 - dog
B1 - cat
C1 - mouse
I want the formula to give me "yes" if dog is present.
=IF(A1="dog", "yes", "no") is for one cell. However if I do it this way:
=IF(A1:C1="dog", "yes", "no") I get an error.
In Worksheet 1, Cell B63 I would like to create a drop down menu, with two options for the user to select - 0.05 and 0.01. I would like each selection to then control the formula in the cells C63:L63, for example;
Where 0.05 selected it computes for cell C63:
=IF( COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!N55>'WORKSHEET4'!L55,"YES","NO")
then cell D63
=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!AC55>'WORKSHEET4'!AA55,"YES","NO")
and so on
Then if 0.01 selected it would compute for Cell C63
=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!N55>'WORKSHEET4'!K55,"YES","NO")
then cell D63
=IF(COUNTA(C4:C54)=0,"",IF('WORKSHEET4'!AC55>'WORKSHEET4'!Z55,"YES","NO")
I would like to be able to count the amount of entries in column C and depending on the amount group them in either groups of 3 or 4, all names would be unique...so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appearon a seperate worksheet say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data). There will never be groups of 5 or more and never less than 3
View 8 Replies View RelatedIs there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??
View 5 Replies View RelatedRange:
C3,C14,C25,C34,C41
Criteria:
>0
sum-range:
C3,C14,C25,C34,C41
I can't get that to work since the commas in the range are throwing the function off.
range:
C3,C14,C25,C34,C41
criteria:
>0
sum-range:
C3,C14,C25,C34,C41
I can't get that to work since the commas in the range are throwing the
function off.
I know how to find the last row of a continuous range. it's simply:
Code:
someRng(someRng.Count).row
How do you find the last row of a non-contiguous range though? Assume the range is vertically laid out and I don't want to do any looping.
Is it possible to re-size a non-contiguous range? I'm guessing "no", and a Google trawl hasn't provided me with anything.
Here's what I'm trying to do:
dim mult_rng as range
dim rng2 as range
set mult_rng = range("A1:B1,D1:E1")
set rng2 = mult_rng.resize(rowsize:=10)
That is, my original range is comprised of non-contiguous cells in the same row. When re-sizing, I'm trying to increase the number of rows, but leave the columns the same.
Like I said, my gut instinct says that this isn't possible using the 'resize' property. Can anyone think of another method to use? My constraints are that the original mult_rng isn't always the same (and isn't always non-contiguous).
I want data to columns A, C and E, while I have important data in columns B and D. Should I make three queries? Should I make query and save the results to temporary place and then move all the results to those columns A, C and E?
Right now the copying happens like this:
If Not rsData.EOF Then
rngTarget.CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If
So should I do
1) One query and move everything in the right places
2) Multiple queries
3) Something else
Does this depend a lot about how many results I expect? I'd say likely about 100, maybe less than 1500, never more than 10000
I need help to this : When i type a number to a cell and press enter , i want to check if this number exist in a range of cells (in sheet 2) , and if exists , excel show me a message. Actually i use it for my *** club. Number is the client code. When i writte 50 in a cell , i need from excel to check if this client own me money , and show me some message..
View 8 Replies View Related