Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


VBA Find Cells Containing Numbers Or Alpha

i need to see what cell in the collum is empty. how can i do that in vb?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Find Cells Contaning Text Not Numbers
I need to find cells containing text (Alpha characters only) in a single column, an return the row(+1 then -1). The column contains Group names (Unknown) , and under each name follows numeric data until the next group name. The cells are not formatted other than BOLD names. I need to exctract the numeric data in groups.


I have manged using the following code, but this always assumes there is an "e" in the name, but would prefer a more foolproof method. (I used "e" cos it is ok so far, BUT this could change in the future)

Sc = "A"
Sr = "1"
With Worksheets(1).Range(Sc & Sr & ":A500").................

View Replies!   View Related
Sort Column Of Number & Numbers With Alpha Characters Attached
Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.



Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:


View Replies!   View Related
Find Duplicate Table But With The Numbers In Each Of The Cells Doubled
I have a table that is 94 columns *12 rows each cell has a different number I need to find a way to duplicate the table but with the numbers in each of the cells doubled.

View Replies!   View Related
Remove Non-alpha Characters From Alphanumerics With Option To Remove Numbers
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).

Option Explicit

Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function

Two requests:

1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?

2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"

View Replies!   View Related
Compare Adjacent Cells, And Split Alpha Numeric String
I have another problem with this damn address file. Column H and I have data in them that is often mixed. As shown below, I have used A and B below, but its normaly in Column H and I. I would be greatul if some could write a macro to split the data into the two columns.

Rows 2-8 is what Im presented with. I would like them to look like 11-17

Note that the number in row 8 does not match, so is left for manual intervention....

View Replies!   View Related
Changing Cells From Alpha To Numeric And Now My If Function Won't Work
I had an "If" formula that would grab my numeric data from one cell and dump it into the corresponding cell depending on my letter code. I had to change my code vales from apha to numeric. I enclosed the attached spreadsheet and highlighted the cells in yellow that are in my formula that don't work any longer. I was hoping someone may have a solution.

View Replies!   View Related
VBA - Checking For Blank Cells And Ordering Numbers
I have the following macro:-


Selection.Sort Key1:=Range("B2:B500"), order1:=xlAscending, Key2:=Range("A6:A250"), order2:=xlAscending

Within this I would like to add a couple of features which check:-

1) If there is a number in column A - there should be one in column B
2) The numbers in column B are sequential i.e. 1, 2, 3 etc without any gaps in the sequence

View Replies!   View Related
Vba To Find & Move Cells
way to find all the cells in range A1:A200 that partly contain the string "FindThisWord", and then cut & paste each of those cells 1 row above and 2 columns to the right?

View Replies!   View Related
How Can A VBA Macro Find Which Cells Are Being Referenced In A Formula
I have a spreadsheet with a number of different formulae on it. I have a macro which I want to extract which cells are referenced in the formula in the active cell and then branch based on the which cell is referenced.

Is there a way to find the referenced cells without parsing the formula string?

View Replies!   View Related
VBA Solver: Find Minimum Value Of Cell By Changing X Cells With Constraints
I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time.

Here is the ....

View Replies!   View Related
Find The Formula That Is Used To Find These Numbers
The first 7 digit number is the input to a certain equation. This equation outputs the 7 digit number after the space.

Input (7146127) ==> Equation ( Unknown) ==>Output (2871082)

These are some data that I have ( inputs and outputs). I was wondering if there is anyway to find this equation. I can get more data if that helps in finding the equation.


View Replies!   View Related
Find In VBA Code: Find Exact Match
I'm using the below VBA

Dim c, DataRow
With Data
Set c = . Range("A5:A350"). Find(KPI, LookIn:=xlValues)
If Not c Is Nothing Then
DataRow = c.Row
End If
End With

Now,If KPI is for example = "Favourite Hotel" and if i have data that looks like :

Favourite Hotel - London
Favourite Hotel - Cardiff
Favourite Hotel
Favourite Hotel - Birmingham

Then it seems to not look be looking for an exact match (e.g. Favourite Hotel) and rather is finding the first item in the list that contains the KPI string (E.g. Favourite Hotel - London).

How do i make it search for an exact match?

View Replies!   View Related
Find Missing Numbers
I have a file with approx 3600 rows. In Col B there is a 13 digit EAN-13 code. I would like to find out which numbers are missing. I have to compare each cell from top to bottom in the B-column to the cell below and find out if there are some missing numbers. If the numbers are incremental then goto next cell. But if there's a gap in the numbers then it should insert the missing numbers with an increment of 1.
I think the logic statement should look like this:

i = 1
For x = 1 to last row
If the gap between B(i) and B(i+1) < 1 then
Insert a row and put the missing value in the cell on the new row.
i = i+1
Next x

The comparison has to be made from the 5 digits in place 8-12 in the code.

Here's an example (i've dimmed the digits that shouldn't be compared):


As you see there's a missing number between the third and fourth row. Here the value should be 733098592074 + the checksum that is a bit tricky to calculate.
Between the fifth and sixth row there are four missing numbers that has to be filled in.
The checksum is OK to leave out at this moment. The important thing is to get the 5 digits.

View Replies!   View Related
Go To Next Worksheet To Find Corresponding Numbers
I have a file with 2 worksheets: Worksheet "Data" contains Store Locations, Store Numbers, Categories, Brands, Sales, Clusters, % Demand, etc.

Under % Demand on Worksheet "Data", I need a formula to look for % Demand from Worksheet "Stores" that related to the same categories, brands and stores numbers.

View Replies!   View Related
Find The Frequency On Numbers
(1) i have a project of a series of numbers running left to right in a random order what i am after is to sort them into order

(2)i want to find the frequenzy on numbers and how often they apear with other numbers compairing one line to the rest all the way threw

View Replies!   View Related
How To Find The Most Common Numbers
I have 300 number groups and each group has 22 different numbers (from 1 to 80).

Can I find the most common 5 or more numbers in these groups by excel, macro or any program?

for example, let us suppose that 4-15-23-36-45 are the most common numbers and these numbers are in 8 groups from 300.

View Replies!   View Related
Find Average Of Last 4 Non Zero Numbers
I am trying to figure out how to take the average of the last 4 "non-zero" numbers in a series of values.

The sheet looks something like this, numbers are all in one row:

7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32 , 7 , 7 , 7 , 7 , Total = 28 , 8 , 8 , 8 , 8 , Total = 32 , 0 , 0 , 0 , 0 , Total = 0 , 8 , 8 , 8 , 8 , Total = 32

I'm looking for a formula that will look at all the last few totals (starting from the end) and take an average of the last 4 totals that were > 0. In this case it would average {32, 32, 28, 32} ignoring the 0 total.

View Replies!   View Related
Vlookup Cannot Find Numbers
I have a very large database (42455 lines) which has three columns. The column A is a shipment number which is a combination of numbers and letters, column B is a ship date, column C is a dollar value for each shipment. This range (A2:C42455) is named SHIPMENTS. I have a second database right next to it that contains warranty information. Column D has the shipment number, Column E has the date the warranty call came in. These last two columns have only 2788 lines.

I'm using VLOOKUP in column F to match Ship dates with Warranty Call dates. The formula is:


and was dragged down all 2788 lines.

Out of the 2788 lines it did not find dates for 301 of them. Some of those 301 have Shipment numbers that are only numbers (no letters). As a test I copied one of the "unfound and all numbers" shipment numbers and did a control+F for all of column A and it found it. Clearly VLOOKUP is missing some of the Shipment numbers. I made sure that the Shipment number in column A matches the number in column D by cancelling out of the Find window and clicking on the found cell. Then I made sure there are no stray spaces tacked onto the end of the number and there were none. I hit enter.

At this point excel starts calculating again and when I go back up to the VLOOKUP (In column F) it has now found the data I've asked it for originally. I've not changed any of the values of either cell...All I've done is use Control+F to manually find the Shipment number in SHIPMENTS and then verified that there were no spaces in the found cell by clicking on it and using the arrow keys. Once that was done I hit enter.

Somehow this sequence of actions allows VLOOKUP to now find the cell.

View Replies!   View Related
Separating Alpha And Numeric
I have data in column with combination of alpha-numeric like "Member # 308166 RCI Number 4801-60436". can I split this without using copy and paste?

View Replies!   View Related
Add Alpha Character
I want to add a letter to the end of value and have it start over at "A" when the cell value changes. For example

My Column contains

I want this in another column

View Replies!   View Related
Limit First Character To Alpha
I'm trying to have the macro limit the cell entry to 5 characters beginning with a "P" and the remaining 4 character being numeric. The problem I'm having is that it's not recognizing the requirement for the beginning "P" but only identifies if the length is less than 5 characters. Any idea how I can resolve this?

For PN2 = 43 To 72
If Not IsEmpty(Cells(PN2, 1)) And Left(Cells(PN2, 1), 1) "P" And Len (Cells(PN2, 1)) 5 Then
MsgBox ("The employee number must be 5 characters long begining with P (e.g. P0001), please change accordingly!")
Range(Cells(PN2, 1), Cells(PN2 + 2, 1)).ClearContents
Cells(PN2, 1).Select
End If

View Replies!   View Related
Alpha Numeric Sort
I'm doing a sort of product codes which are of different character length with some numeric and others alpha-numeric.

A small section of the sort is below.

I want it to end up like this:


But the sort ends up like this:


In other words, I would like product codes where the last character is a letter to be sorted ahead of product codes where the last character is a digit. The way Excel is normally set up, it sorts in just the opposite way.

View Replies!   View Related
If Statement With Alpha Characters
Attached spreadsheet - the cell highlighted in yellow.
This is the formula I currently have, but it isn't giving me the results I am looking for:
=IF(TYPESVC="CompanyX", COUNT(Companies!I:I), " ")

Basically, IF the Companies tab, last column has 'CompanyX' written in it - then I want to count the number of responses provided in the Q2N column.

Same formula would be copied over to the other cells to the right as well (for Q5, Q6, Q7, etc.)

View Replies!   View Related
Numeric Alpha Sort
I have an application where there is a column A with values of F1 through
F83. There is data in columns B,C,D,E,F AND G.

From time to time there is an addition in column A, the value being F1A or F2A etc.
The rows then have to be sorted so that F1A follows F1 and the next row would start with F2 rather than F10.

View Replies!   View Related
Next Alpha Number Formula
I am trying to create a formula (or if VBA is better) where when a name is put into a field the next alpha mumber is created. Have attached example.

View Replies!   View Related
Macro To Find Middle 3 Numbers
Dim varUserInput As Variant
varUserInput = InputBox("Enter 3 Digit Number:", _
"Project Number", "")
If varUserInput "" Then Exit Sub

View Replies!   View Related
Find And Replace :: Code Numbers
Hey Have a spreadsheet with about 150 worksheets in.

In Column B of each worksheet is a list of packages


Value Added Processing
Business Viability
Climate Change

I need to add into that other cells to further divide those packages so it will end up looking like

Value Added Processing
Business Viability
Climate Change

I have a separate worksheet which has the 37 different package types along ROW A from Column A to Column AH, then going down underneath each of the heading is the various code numbers as shown above.

Has anyone got a VBA code which will allow excel to search the worksheets, find the package name and then replace it with the package name but also insert the code numbers below it.

View Replies!   View Related
Find The Position Out Of Played Numbers
*ABCDEFGHIJKLMn8OPQRSTUVWXYZAAABACADAE1n1n2n3n4n5n6n1n2n3n4n5n6n7n8n9n10n11n12n13n14n15n16n17n18n19n20n21n22n23n24Position 24567141517192223272834353639404143464748493;223268103048456714151617192022232830343637383941434446485;16;20:24421432364148456791014151718202328303438394041444647485;9;2159173537384645679101416182023273032343839404144474849620253542454645679101416182327283234353738394043444647497131423414748567810141617202528303435363839414244454647

I have selected numbers to play in the table shown above from cell G2:AD7 and celebrated draw result numbers are shown in cells A3:F7

Now, suppose I want to play the numbers, which are in cell G2:AD2 and the draw-celebrated numbers are shown in the cell A3:F3.

Now I want to check cells A3:F3 with G2:AD2 and want to know in which position are the celebrated draw matched with the numbers played.

In this case the result in AE2 will be 3; 22 that means only two numbers are matched with the played numbers. Those numbers are the number ‘6’ which is in the position 3 counting from G2 and number ‘48’ which is in the position 22 counting from G2 again.

The result must be shown in column AE for all the draws as shown in the table above.

View Replies!   View Related
Find All Numbers That Equal To 9 In The Range
I have a range of values in a1:f500 the values are all numbers between 1-25. I then have a value in cell h1. what i need to do is find all numbers that equal to 9 in the range a1:f500 and replace all these 9's with the formula =$h$1 so that i can simply change the values in the range a1:f500 by changing the value to say 12 in cell h1. could someone please text me the vba code that needs to be entered for sheet 1?

View Replies!   View Related
Find And Replace Phone Numbers
I have a bunch of phone numbers that I'm trying to reformat.

Say I wanted to replace any phone number like this: 1##########

With this: ##########

Keeping the numbers intact, just getting rid of the 1 in front of them. Can't figure it out for the life of me.

View Replies!   View Related
Find Sum In List Of Of Numbers
I have a list of numbers in a column and I need to find which numbers
when summed together equal a figure. I have a list of invoice amounts
that I need to match up with payments (the payments are always made for
several invoices so I need to come up with sums of several invoices to
get to this payment amount).

An example would be I have this in the following section (A1:A10):

I need to find which combination of these figures would sum $1,173.76.

View Replies!   View Related
Find Missing Numbers In A Column
i had a worksheet in which i had a column Named Cheque No. i want to print the missing cheque no's in a series of cheque no's.The logic here is that there will be different series of cheque nos for different transactions.Just check out my worksheet.

View Replies!   View Related
Find In A Range Of Serial Numbers
Sheet4 in my book looks like this:

I......Invoice No…..…Starting Serial No……...……Ending Serial No
2…………ND12………..6011000000013652……. ..6011000000103652

note that the last 4 numbers of the series are randomly generated for security reasons and are not part of the range of the the serial No’s

So Invoice ND = 10 consecutive numbers, Invoice ND13 = 14 and Invoice RF4 = 1000 and RF4(again) = 8

The list on this sheet is a mile long

What I need is a “search box” with two windows in it

In one box I will type a serial number and in the second box I will get the Invoice number

The challenge is that the serial number could be between Starting and Ending serial No’s thus if I were to type serial number
6006000001086598 in the first box the answer in the second box will be : RF4

View Replies!   View Related
Referencing To Find Pull Numbers
I want to input numbers into a master sheet by having it referenced to a name. For example; I will be inputting a report on monthly sales, which contains a company name, and then a value. However, this report doesn't always necessarily contain all the companies listed on the master. I want to simply put this report on a page, and have the referencing pull the numebrs to the approriate company on the master page.

In the attachment - I want the numbers from Sheet 2 to be entered into their appropriate match in the Master sheet (sheet 1) - without copy and pasting (becuase in reality, I have 200+ company names and numers I'm dealing with).

View Replies!   View Related
Find And Highlight Specific Numbers
I have a spreadsheet with a stock list. Each item has an individual number. I have a code below, which I have doctored from other various sources. The problem I have is that if I search for number '12' the spreadsheet highlights every cell with 12 in (120 121 122 123 etc...) Is there any way I can change the code so that it returns only the 12 and not every cell that contains 12? The current code is as follows:

View Replies!   View Related
Find The Numbers That Add Up To A Certain Number
I have a worksheet that has a column with amounts in it. I would like to find the numbers in that column that add up to a certain amount.

View Replies!   View Related
Find Common Numbers Between 2 Columns
I need to find out common numbers between columns. Each column has multiple number entries seperated by a comma. For example:

Column A: "5592,12222" and Column B: "1,2,3,4,5592,123123" and Column C: "3, 4"

I need to find out any numbers that are found in more than 1 column. Hence, the result should be something like "3,4,5592". If no match is found then maybe can indicate by coloring the cell. My data is over 50,000 rows and 30 columns. I need to do this for each row one by one.

View Replies!   View Related
Find Values Not In A List Of Numbers
I have a table with numerous business units in column F. The business units are shown as a number. i.e. Business unit # 00007, 00041, etc. There are around 500 different business unit numbers. I have a list of 60 specific business units that I want separated, leaving a remainder of 440 others. I want these 440 other business units to stand out, ideally with conditional formatting.

View Replies!   View Related
Find Numbers In Text Strings
I have several cells in a column that look something like this:

Cell A1: abc 1234 def ghi
Cell A2: xxxx aa b 245 qqqqq
Cell A3: abcdefg hij kl mnopqr s

Is there an excel formula or combination of formulas I can use to identify:
(1) whether any given text string (such as those above) include numbers, and
(2) what the first number (which could contain 1-4 digits) contained in the text string is?

View Replies!   View Related
Examining Alpha Numeric Data
I am trying to make a worksheet change the fill color depending on the data in the cells. I have two issues I cannot find the answers to.

View Replies!   View Related
Max Of An Alpha-numeric List
I've got a list of reference numbers in column A


And I need to find what the biggest number is without resorting the list.

View Replies!   View Related
Can A Vlookup Import Alpha Text
Can't seem to get a VLOOKUP to work and import alpha text such as a person's name. Is there a formula that would do this similar to a VLOOKUP?

View Replies!   View Related
Extract Alpha Numeric Values
I have cells with alphanumeric values. I need to extract the numeric values based on certain criteria and put these values in adjescent rows.

The sheet I am using has 3 rows . Row B contains data. Row C is Amount, Row D is Month, Row E is Action

To put values in Rows C, D and E , I need the data from row B in following criteria.

data in B row is as follows :-

RS.555/STMT 020907/BP12/ OFFER
ANNFEE RS 555/- / STMT 020907 / BP 12 / FIRST TIME
RS595/STMT 0807/BP12/ first time
MEM FEES RS 525 /STMT 120807 / BP12/1ST TIM

criteria for row C
if the cell contains "AMT" or "RS" or "INR" or "AMNT" or "RS."then extract the value till first "/" or "" and put it
under Amount row. ( E.g: "AMT 595 /" or "Inr 595/" or " Rs. 595")

criteria for row D
if the cell contains "stmt" or begins with "0" or endswith "07" then extract the value till first "/" or "" and put it under Month row.

criteria for row E

if the cell contains "BP" or "OP" then extarct the value till first "/" or "" and put it under department row. ( e.g " BP01 /" or "OP 02 ")

View Replies!   View Related
Covert Numeric Value To Alpha Code
I've tried searching the forum here, but have not seen anything like my problem as follows:

I would like to convert the numeric value in a cell to a coded alpha

the numbers 1,2,3,4,5,6,7,8,9,and 0 would be coded as B,L,A,C,K,H,O,R,S,E respectively.

For example, if cell A1, held the value of 5750, the formula in cell A2 would result in KLOE.

The figures in Col A would be no smaller than 3 digits nor larger than 5 digits.

View Replies!   View Related
Return Number Before Alpha Character
I have an Excel file that quiries Google for driving distance (someone helped me with this.) It returns the correct miles but has some 'extra' characters. Example:
31.1&nb or 886&nbs

So the answer always has &nbs after the mileage. I tried the following function which works when there is a decimal but fails when there is not. =REPLACE(MID(E2,1,SEARCH("&",E2,1)-1),SEARCH(".",E2,1),1,","). Can this be done with a function or must it be VB script? Which is fine if it is.

View Replies!   View Related
Using Vlookup To Find Phone Numbers Online
How can I create a vlookup function to automatically retrieve phone numbers off using my current mailing list?

I have a list of more than 4,000 of my customers. I want to ad in phone numbers automatically. I currently have the first and last name in one column. The address, city, state and zip are all each in their own column.

I have never use vlookup before, nor have I ever used excel other than for data entry. What is the easiest and most efficient way to do this?

I searched this site and seen vlookup mentioned for this type of automatic data entry. There may be another way or method I am not aware of.

View Replies!   View Related
Find Last Used Cell And Align All Numbers In Same Row
Example Table A:

*ABCDEFGHIJKL1No. 1No. 2No. 3No. 4No. 5No. 6No. 7No. 8No. 9No. 10No. 10No. 10221418269124333324171165141728884321000125067775100860615906015712170712180910

Result Table B:
*ABCDEFGHIJKL1No. 1No. 2No. 3No. 4No. 5No. 6No. 7No. 8No. 9No. 10No. 10No. 10263214854241121253210609433610178011412288370151076151767788070121206015907910

What i want here is to align all the numbers considering the last used cell. In example table a; the last number is 0 (F8). Considering that, in the Result table B all the numbers are aligned in row 8 from the bottom to top giving an equal alignment to the table.

View Replies!   View Related
Allocate Numbers To Letters And Find Average
formula to do the following:

Assign numerical values to the letters G, A, R each having the values of 3, 2 and 1 respectively and then take an average of their values. Please be aware that in some cells there may be no letter.

So in a 5 cell range the values could be:

blank, G, A, R, blank which equates to a result of 2 for an average, (3+2+1)/3 (the two blank cells are discounted).

View Replies!   View Related
Find A Combination Of Numbers That Equal A Given Sum
If I have A1 = 1, A2 = 3, A3 = 5, A4 = 7, and A5 = 9 in column A, Would someone know how to get the combination of numbers in column A that equal 13, or 24 for example?

View Replies!   View Related
Copyright © 2005-08, All rights reserved