I am working on code to detect whether a cell contains an even or an odd number in it. I can do this when I know that the numbers I want to evaluate are in cells A1 trough A10 but the next part of the code is for when we do not know how many entries there are and we have to evaluate until we come across an empty cell and then stop.
View 2 RepliesI have the following code that I use to create individual client files from one master spreadsheet. My problem is that it always creates and saves a blank file with only the 'rn' value in the file name. It seems that it is looping one extra time when the 'rng.value' list ends.
Sub MakeFiles()
Dim wb As Workbook
Dim ws As Worksheet
Dim criteriaRng As Range, usedRng As Range, rng As Range
Dim lh As String, ch As String, rh As String
Dim rn As String
[code]....
I would like to extract competition naps from the competition entries and then remove the string ending "nap" from the data so it can be pasted to another sheet in it's 'clean' format.
on the example sheet column B will always be the first column and the range will extend to either G,H or I (always the max range as there are always 6 to 8 races)
i would like the code to search through these columns of data and find the cell ending in 'nap' then to return this in column i, once done remove 'nap' ending from both the source cell and column i.
once done to then go through all the rows and do similar
the end result would be all naps returned into cell j and all the 'nap' endings through the data range B to I (max) to be removed to leave the horses name only.
in book 3 the first row nap was sommersturm so i have shown the outcome i would like with the nap ending in I1 removed and the horse name returned in J1 again minus 'nap' ending.
the data will always be clean with no leading/trailing/excessive spaces and always be lower case too. sometimes a space is not in between horse name and nap but it's always last 3 characters i want removed still.
I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
Here's what I tried using macro recorder...
View 4 Replies View RelatedI have a do while loop that runs until the reference cell is empty. This has been working but now I have changed the reference cells somewhat so that the cells contain a formula that returns nothing if a function returns 0 or an error. So the cell has no value but it does have a formula in it. The do while loop keeps running because it thinks that the cell has something it it.
Do While Cells(i, 2) ""
Is there something else that i can put for "" to get the loop to stop when cell contains no value but a formula.
I need a formula to sum all E3 cells starting from sheet "Joe" to the last sheet i have, without being obliged to modify the formula for each new sheet i create and which by default is created in the end. something like:
=sum(Joe:my last sheet created!E3)
Also,do protected cells in some of the sheets affect this formula calculation?
Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.
[Code] ......
I have a code now that in excel, when the button is clicked, will put file into a new email and format the email.
What I have been trying to do, is make it so that if Cell C10 is blank, you get a prompt telling you it must be filled out before you can proceed with the submission.
Here is the code now:
Sub SendTimeCard()
On Error GoTo err_handler
Dim OutApp As Object
Dim OutMail As Object
Dim subject As String
Dim body As String
' Be sure the workbook is saved first
ThisWorkbook.Save
[Code] ........
Short and simple. What is the quickest, easiest & most efficient way to find the first blank cell within a column using VBA?
How to modify a custom cell format to make data entry easier.
My goal is to have the output look like this: 'A12B-C20D: SET 10: Text string here'
Inputs needed are:
1-3 digit number between A & B
1-3 digit number between C & D
1-2 digit number after SET
Text string of variable length
Where I am stuck. So far I can get "A12B-C20D: SET 10:" to show up properly when 122010 is input. The custom format I am using looks like this: "A"##"B-C"##"D: SET "##": "
When I try to incorporate the "@" symbol the first part of the formatting goes away and the output shows up as "122010 Text string here".
I have tried:
"A"###"B-C"###"D: SET "##":";;-;_@
"A"###"B-C"###"D: SET "##": ";;@
"A"###"B-C"###"D: SET "##":";_@
"A"###"B-C"###"D: SET "##": ";;;@
I need to accomplish selecting a range using variables for the starting cell and ending cell rows and columns.
Pseudo code (how I'd like the syntax to be, but isn't):
Worksheets(mysheet).range(startCellRow, startCellCol : endCellRow,endCellCol).Select
With Selection
...
End With
Looking forward VBA coding for:
If input is entered into column C (range C2:C100), then row cells for columns D, E ,F, H or I must NOT be blank upon save.
Could be that one or more of these row cells are left blank by mistake.
An error msg pops up upon attempting to save, stops the save and colors each cell yellow that needs info entered into.
Using Excel 2010.
the followng code should execute once but it seems to just keep going round in a loop.
View 12 Replies View RelatedI am trying to start in cell A1, and loop down. Each time a value is found, I want to insert a blank row, which is working, but I just can't figure out how to end this, it goes to the very last cell in column A and gives a debug error. I would like to to just go back to cell A1 after all rows have been inserted.
Sub Insert()
Range("A1").Select
Do
Selection.End(xlDown).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""
End Sub
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies View RelatedI have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
View 8 Replies View RelatedI presume this is fairly simple to do, since it's certainly easy enough to do manually by filling in a couple of rows and dragging them down, but I need it to be performed in a macro that I can run before other macros run.
What I need specifically is for the macro to go to G1 and insert the number .01... Then go to G2 and insert .02... Then G3 and insert .03... And repeat this until it finds the first non-blank cell ( row number this occurs at varies), at which point it ends and does nothing to that populated cell or any other cell in the column thereafter (including other blanks farther down).
This all needs to be done in Arial, 10pt, white.
I have a list that looks something like this:
Column B
Row 4 Item 1
Row 5 Item 2
Row 6
Row 7
Row 8 Item 3
Row 9
Row 10 Item 4
Row 11
Row 12
Row 13
Row 14 Item 5
The range of cells in column B containing the items has a name "ColStreams"
I need to go through the list, filling in each blank cells with the value contained in the first non-blank cell above it - so, in this case, rows 6 and 7 would contain "Item 2", row 9 would contain "Item 3", rows 11-13 would contain "Item 4" and so on.
I'm looking for a way to take a number (23.89145 or $3.45 for instance) and use a formula to force it to end in a 4 at two decimal places (23.84 or $3.44)...generally only interested in going down.
View 3 Replies View RelatedI have a macro that continues to tell me that an End With and/or End Sub is expected. I continue to add these statements in various ways, but the macro will not complete properly.
Here is the
I have a macro I use cleanup up excel data I paste into it. Currently I paste in data that is three columns but there is space between the different items. For example:
View 2 Replies View RelatedIn my sample workbook, (attached), all I want to do is up a formula in column E that compares values in columns A, B and C. If they are the same, I want to put the last year model in Column E.
I have filled in the first 12 rows to whow the desired result. Ultimately, for each part number and model combination, I want to put the final year for that combination in column E. The sample workbook constains just a few rows, but my master has about 30,000 rows.
After the last line of data, I do not want ton's of blank columns. I know you can scroll down with your mouse to see more columns, but after I run my VBA script, there is a good 1200 blank rows below the last data row.
Is there a VBA line to say end after last row containing data.
Ex. I have 335 rows of data. So there should be like 350 rows total (with a few blanks preferably none or 1, but not sure how hard this is) instead of 1500 total.
Reason I ask is that for some strange reason a program I use is pulling in those extra blank lines which is screwing up my import job.
The formula below calculates appropriately, however, if any of the cells (E12,E14, E21, E22, E28, E29) are blank, it returns a #VALUE! error. I would like the cell to remain blank. How can I do this? The formula is listed below.
=(SUM(E34-E6)-(SUM((E12)+E14)-(SUM((E21)+E22)-(SUM(E28)+E29))))
I have data that is added to every week. I need an equation to count how many times each employee show up each weekending.
View 3 Replies View RelatedI am trying to create a week ending formula in excel. I have a list of dates and in a new column I would like to display the week ending of these dates. I want the weeks to end on Sunday. For example, if the date column has the date 7/24/12 in it, I would like the week ending formula to spit out 7/29/12. What formula would achieve this?
View 5 Replies View Relatedwhere i can grab a number from a cell it would ALWAYS be the last string item..
for example if cell A1 has the following text:
04/02 Card Purchase 03/31 Google*1873060297 Cc@Google.Com CA Card 5036 $500.00
i want cell B1 to hold just the $500
this wold always be the case for everything im trying to do
Here's my problem. I need a formula to take the decimals out of a column but keep ending zeros. I have tried find/replace & several formulas but nothing seems to be working. Here is an example of the numbers in the column and the end result I need.
9.66 966
0.50 050
13.09 1309
309.98 30998