Non Numbers Cells
I would like to know if I can create a Macro that will do
a search in column A (from A2 to A65536) and every row
that has anything else than a number, delete the entire
row (not just the cell).
try the following macro:
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If not isnumeric(Cells(row_index, 1).Value) then
Application.ScreenUpdating = True
>-----Origi...Merged cells and Pivot table
I have the following tabel
A B C
Region Customer Product
1 ASIA Custo Apple
Since one customer can have more products and a region more customers I used
in the table mergd cells to keep the overview. However when I am using this
data in my pivot table only the first complete row is stated in the pivit
table. Any ideas how to solve this?
Select your table, unmerge all the cells, then use Edit Go To... Special Blanks.
Then press = and eith...Help Adding up multiple calculated cells
I have 8 columns that MAY contain NOTHING or may contain text chosen
from a lookup name from a data validation pick list. Lic3 won't
contain a value UNLESS Lic1 AND Lic2 are populated.
I have 8 columns range named Lic1, Lic2, etc to hold a chosen license
I have 8 other columns used for number counts relating to the lookups
I have range named Lic1Cnt, Lic2Cnt, etc which hold a number such as
I need to add up the value calculated for each Lic based on looking up
the text in a table, getting the price and then multiplying by the Lic?
Cnt number. Some records may have only a singl...Count of cells since first purchase
Looking for a bit of help with the below.
I want to calculate how many weeks have passed since a customer first
purchased from me. So for example see below. I want to calculate in cell E2
how many weeks have passed since Account 1 first purchased from me. In cell
E3 i want to calculate how many weeks it is since Account 2 first purchased
Week 3 April Week 4 April Week 1
Account 1 2
Account 2 1
Account 3 ...Making a MsgBox return cell data when search finds different data in an adjacent cell
Thank you for helping me with this in advance. I'm grateful for all
the help I've found in these groups.
I'm running a macro that is searching a sheet for data. When it finds
that data, I need a msgbox to pop up with the data from that column's
I have it searching the sheet and finding the data listed in the
Because of the possibility of multiple winners, the msgbox will pop up
each time someone scores.
This macro works fine as it is except I would like to add the winners
names to the message boxes.
With Worksheets(1).Range(&quo...VBA code for copying on a range were cells are blank
Hi everyone, me again!!
So, you were all so helpful with my earlier prob, thought i'd keep you going
with another prob i'm having.
OK, so what I want to do (in VBA if poss), is to search the range B2:E2 and
see if they are blank. If yes, then I want to copy the cells above and then
carry on searching that range. I can't do it upwards as its likely that
there are going to be more than one set of blanks, e,g.
A B C D E
1 Joe Bloggs | 309873 | Smith, John | 08:00 | 12:00 | Back Office
2 | | ...Formula to strip figures from cells text strings
I'm trying to write a cell formula that strips figures from cells text
strings & totals them into one cell. The figures will always have a
Cell A5 has a text string "924 Social 9.78"
Cell B5 has a text string "984 163.94 981 7.84"
Cell C5 has a text string "920 Kitchen 7.86
Cell D5 has a text string :988 Youth 42.46
Need to arrive a the total amount of 231.88 in cell E5
(9.78+163.94+7.84+7.86+42.46)-do not include the non-decimal figures
924, 984, etc.
Thanks for all your help.
--------------...Copy contents of a cell into a blank cell
How do I copy the contents of cell B2 into cell D2 only if D2 is empty
It cannot be done with a formula - only with VBA.
The following code should achieve what you described
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$D$2" Then
If Range("D2") = "" Then Range("D2") = Range("B2").Value
Copy Code above
Right click on sheet tab>View Code
Paste code into white pane that appears
Alt+F11 to return to Excel
"fire9...Specifying Cells to Count?
I am having trouble adding in Excel... I have a list of IDs in A:A, and
a list of numbers in B:B. For example:
Now, I need to calculate the number of instances of 0.1 corresponding
to the value of 1 in A:A, the number of instances of 0.1 corresponding
to 3 in A:A, and so on. I've tried FREQUENCY(), COUNTIF(),and a few
I know the number of instances that 1,2 and any other number shows up
in A:A, and I think I can use COUNTIF() -- however, I can't find a way
to dynamically specify the number of...How to keep a cell empty at the same time formula embedded?
Can we have an empty cell but formula embedded in that cell.
A cell cannot be both "empty" and contain something...........you can
however, put a formula there that will display a blank cell according to the
conditions of it's calculations...........
=(IF(A1>5,A1,"")......will display a blank cell if A1 is 5 or less
Vaya con Dios,
"vic_dic" <email@example.com> wrote in message
> Can we have an empty cell but formula embedded in that cell.
What are you...Cells formatted for general and text show scientific format
I am working on an Amazon.com shipping confirmation
upload. The workbook is downloaded from amazon.com. When
I enter the Order Item ID number which is 14 characters,
it displays in scientific format. I have tried general,
number and text formats. Number format works if there is
no leading zero but general and text do not.
- Are you formatting the column for Order Item ID number
before entering the data? That should solve the problem.
- If you are formatting after entering the data, selecting
F2 (Edit) in a cell will then put that cell into the
correct text format (except for leedi...More than 3 on Conditional Formatting
Can it be done?
I don't want to have to write a routine in VBA to scan all the cells just to
change the colour.....
i.e. 1 is red 2 is a lighted red, 3 is amber 4 is a ligther amber, and 5
green and 6 a lighter green...
I need some help here guys...
Anyone point me in the right directions...
modify one of these. The first one belongs in the SHEET code module.
right click on the sheet tab>view code>insert this>save
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Select Case UCase(Target)
Case "...Knowledge Base
I am having a heck of a time using this new knowledgebase. I have yet to get
any relevant responses no matter how I word a search. I was able to get the
old knowledgebase a few times but I can no longer find it.
Any suggestions about how to use this? or better yet, how to access the
Are you referring to Customer Source? I also have issues with it, and always
end up coming here to find my answers!
"Tracey D" wrote:
> I am having a heck of a time using this new knowledgebase. I have yet to get
> any relevant re...Formula for selecting a varying cell reference
I'm trying to tie 2 worksheets together. The first is a data entry sheet,
set up by month. The second is a sheet that I want to pick up monthly data
-- i.e., the most recent month of information entered on the data entry
sheet. Is there a formula I can write in the monthly sheet that will look at
the data entry sheet and go to the last monthly column that has data and
retrieve that? I guess I'm thinking like a flowcharting yes/no process
starting in December -- i.e.,:
Is December blank? Yes -- go to November; No -- use this data -- etc.,
...Storing VBA Code In a Cell to Use real Time
I want to store multiple values in Column A and VBA Conditional Code in
Column B. The Conditional Code is just text and is just stored on the
worksheet. When a macro is run and a variable equals the value in column A
then the code stored in Column B executes inside the macro. For example, A1
= "ALLEX" and B1 = "If Not Left(route,1) = "K" . So, when the macro runs and
checks to see if "ALLEX" is in Column A. If so ,then it will use the
associated Condition: If Not Left(route,1) = "K". directly in the Macro.
So, if both condition...Conditional Formatting with Text
How can I tell CF to turn a cell green if the cells
contain text "great" and red if the cells
TX a great deal
cell value>="great">format>patterns (choose green)
cell value>="great">format>patterns (choose red)
>How can I tell CF to turn a cell green if the cells
>contain text "great" and red if the cells
>TX a great deal
Select the range of cells
Format > Co...Clearing Check Boxes and Cells
Ok, this is either going to kill me or make me stronger..........
I have successfully combine my "clear" functions into one button now
(clearing text/numbers from cells and clearing checks from
NOW....... NOW.......... my problem is that when I protect the sheet........
the "clear" button that worked perfectly gives a "Run-time error'1004',
Unable to set the Value property of the CheckBoxes class".
If I turn protection back off, the button works perfectly.
I have to be able to protect the sheet so users can only check boxes ...NAICS Code
I have a list of NAICS codes formatted as numbers that I would like to
format as hierarchical text.
Does anyone know how to change the cell format so that the "Current
List" becomes the "Desired List"?
Thank you in advance for your help.
It looks like you could preformat that column as text and enter your values as
text. Then you could sort the data as text.
Some versions of excel will ask if you want to sort values that look like
numbers as numbers. You should say no to that prompt.
And just changing t...Selecting Cells
Please help, When starting excel, I click on a cell and
get the cross pointer but when I move the mouse to put a
formula or data into that cell all cells are selected in
the direction of mouse movement. I am unable to de-select
the cells and the only thing I can do is close excel from
the task bar. I have come across this problem once before
But cannot remember how to solve it. Thanks in advance for
any help with this issue.
You sould only get the cross hair if you move the mouse
over the box at the bottom right-hand of the cell. This is
used to enter a fill of a s...Custom patterns for cell shading
Is there a way to add 'custom patterns' to the cell shading option?
I was hoping for a nice 'arrow pattern' for the conditional formatting.
(I'm doing a presentation of traffic flow, and for flow north or south [ie
if Northbound > Southbound, flow is northbound], I would like to graphically
show the flow.)
Any assistance or ideas would be greatly appreciated. I'm currently using
windings arrows, but a form of pattern / conditional formatting would be
World of Warcraft *isn't* work?
...Repeating Characters in Cells with info.
I am trying to repeat a period to connect every column in a list of marathon
records. I want it to read
name.............time..............date...............location. how do i
write the formula to do this?
You could give the cells a custom format of:
The *. says to repeat the dot enough to fill the cell (to the right).
You may not want it on the numbers, though.
Stressed Student wrote:
> I am trying to repeat a period to connect every column in a list of marathon
> records. I want it to read
> name.............tim...I want to know which cell(s) are the MAX
Hi everyone, I`m relatively new to Excel but I think I`ve mastered the
basics enough not to get flamed here.
My question is this, I have a bunch of values, that I want to find the
biggest out of, that is easily done using the MAX function. Now, I also need
excel to tell me the number of the cell in which is the biggest, for
example, say we had 5 cash values $3, $7, $9, $10, $3, and those values were
numbered 1, 2, 3, 4, 5 in a list, I not only want to know that $10 is the
biggest, but also that it is at number 4. Is there any way to do this
...Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count
the number of cells in this one column that contains dates. How can I do
If the only cells that are in that column that are numeric (like dates), you can
(Dates are just numbers formatted nicely in excel.)
> Column J contains dates in some cells, and text in others. I need to count
> the number of cells in this one column that contains dates. How can I do
...Why does my mouse not release a range of cells when selected?
When I try to select a range of cells, the mouse will continue to highlight a
range of cells and will not release. I end up having to force Excel to close
and restart. Any help would be greatly appreciated. It does it with most
versions of Excel.
Check if the F8 key was not pressed (you would have the "EXT" mention on the
Excel Status bar), if yes press F8 again.
Apart from that I think this could be a mouse driver problem so to make sure
try and update your mouse driver (esp if you have a wheel mouse).
Apart from that?
"Wolfer50" <Wolfer...How do I lock a formula in a cell in an Excel worksheet?
I created a disbursement worksheet which is simple to use, just involves
plugging in numbers and it figures everything out for us. However my coworker
keeps erasing her formulas wehn entering info.
Is there a way to lock a formula while still allowing numbers to be entered
in the cell?
Thanks for your help.
Sure, ensure the formulas cells are locked and the other cells unlocked.
When you password protect the sheet, make sure the section "Allow all
users of this worksheet ti:" to not have Select locked cells checked.