VBA Find Zero Value (Dash) In Accounting Format

Aug 15, 2013

I have a column of data that may or may not have a formula in them. The cells are formatted in Accounting, w/o the leading $ sign. (i.e. 133.57) The value zero shows up as a hyphen or dash.

The issue is the format of the cell. In that attached worksheet, i inserted two formulas in cell b6 and b7. Both formulas are exactly the same, but the formatting is different. One is in the accounting format, but the other is in general. When I run the code, only the general formatted cell gets found.

My real worksheet is in the accounting formatted code, so I don't want to change my worksheet's numbers into a general format. How I can find the zero value thats in an accounting format?

Below is a strip down version of the code.

VB:
Sub test()[COLOR=#333333]
[/COLOR] Dim rLookInADR As Range
Dim foundcell As Range
Set rLookInADR = Range("b1:b380")
Set foundcell = rLookInADR.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
MsgBox (foundcell.Row)
End Sub
[COLOR=#333333][/COLOR]

View 5 Replies


ADVERTISEMENT

$ Not Showing Up In Accounting Format

Jun 26, 2014

I have formatted several cells with an Accounting style. However, the dollar sign doesn't show up until I click in the command line.

View 5 Replies View Related

Format All Numbers With The Accounting Format

Jan 10, 2007

When working in Excel I format all numbers with the accounting format. I often use the single and double underlining feature on the Font tab of the Format Cells dialog box. Sometimes when I use the double underline it only puts (and prints) one underline. I've worked with the formatting many times with little success. The only way I can get the double underline to show up in this situation is to change the vertical cell alignment to be centered and then increase the cell height. This causes me other formatting heartaches. It's like the double underline is there it just will not show.

View 9 Replies View Related

Custom Number Format - Em Dash For Zero

Dec 19, 2011

I know this works because I have seen it in a workbook at a client's office.

I need a custon number format to substiture an "em" dash for zero. An em dash is a hyphen with the width a the letter m. There is also the so called en dash tht is the width the the letter n. A search of the internet shows that to create an em dash in a cell that contains a 0 ("zero"), hold down the alt key and type 0151 on the numeric keypad. Typing 0150 insert a en dash.

I want to be able to set this up as a custom number format in the custom number dialog.

View 5 Replies View Related

Format Cells To Show Dash For Zero

Jun 25, 2007

In excel if you press F5Special, you will be given to select cells with some criteria but there is no criteria to select cells with zero value in a selected range. Is any such option is available in excel or a macro is needed for the same?

View 9 Replies View Related

Using The Accounting Format With No Decimal Places

Jan 15, 2009

I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:

View 4 Replies View Related

Setting Format Cell Accounting Using Vba

Mar 26, 2009

i am doing some calculations using vba in excel, i need to know how to set the cell format to 'accounting' in vba ?

View 2 Replies View Related

Modifying Accounting Custom Format?

Oct 31, 2013

I'm using this custom format

Code:
_-£* #,##0.00_-;[Red]_-£* #,##0.00_-;_-£* "-"??_-;_-@_-

How do I modify it so that if the value is 0.00 then 0.00 is showed and not the current "-"?

View 3 Replies View Related

Macro To Format Raw Data From Accounting System

Jun 14, 2013

I export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.

Here are the steps I go through:
1. Clear the first 3 rows and the last 5 rows
2. Copy the totals in the last row of the data and paste them in the first row
3. Subtotal the different categories in row 2 and add a validity check
4. Add a new column A with a formula to add customer numbers on each line
=IF(ISERROR(FIND("00000",B5)),A4,B5
5. Copy the formulas and paste values over them
6. Filter the data to show blanks under the "Doc Date" column, delete all rows
7. Filter the data to show blanks under the "Type" column, delete all rows
8. Filter the data to show "Total:" under the "Apply No" column, delete all rows
9. Turn off filters, format all numbers in accounting format
10. Check the validation at the top to ensure no transactions were deleted

View 6 Replies View Related

Choose Different Accounting Number Format / Currency Symbol?

Feb 1, 2012

In the accounting number formats, the available currency symbols are Dollar ($), Pound (₤), Euro (€), and Yuan (¥). But how can I add a custom currency symbol? For example instead of writing "$1,000", I want to write "BDT. 1,000" or "৳ 1,000". How can I do that?

View 4 Replies View Related

Find And Replace Cell That Are Blank With The Dash

Oct 28, 2009

I have a row of 2900 single letter (middle initals) however 222 users have no middle inital. this is a password scheme and need 7 digits, without the middle inital i only have 6. so I want to replace all 222 cell that are blank with a dash can this be done without doing each by hand?

View 4 Replies View Related

UDF - Find Consecutive Numbers In Single Cell With Dash

Mar 14, 2014

I need find consecutive Numbers in a singles Cell but each numbers have a leading zero and "-" (Dash)

My problem is that the UDF that i found on this forum, is for numbers with out leading zero with comma ",",

So even if change the "," by "-", still getting a error Because the Code is designed to Read numbers Formats different than mine..

My Numbers are located in Cell G12 (down), and the message that i need to show in the cell result is :

If Found :
0 Consecutives --> 0
2 Consecutives --> 2
3 consecutives --> 3
4 consecutives --> 4
5 consecutives --> 5
2 Set of consecutives --> 2S

Example of 0 consecutives --> 01-04-07-12-25-30
Example of 2 consecutives --> 01-02-07-12-25-30
Example of 3 consecutives --> 01-02-03-12-25-30
Example of 4 consecutives --> 01-02-03-04-25-30
Example of 5 consecutives --> 01-02-03-04-05-30
Example of 2 sets of consecutive s --> 01-02-07-12-25-26

BTW my numbers start on Cell G12 down..
______G12_______
01-02-03-20-21-25

View 11 Replies View Related

Find Format: Find Method And Combine It With A Countif Or Loop

Feb 20, 2007

I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.

Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub

View 4 Replies View Related

Parse After Each Dash

Aug 9, 2007

I work in the Oil and Gas industry and deal with large data tables that have "Location Codes" for lack of a better term.

The problem is that not all Databases use exactly the same format of code, they will however always have the following:

xx-xx-xx-xx ---> ie. a string of four numbers each seperated by a Dash.

This string sometimes will appear after a text name of varying length. With this in mind I would like to parse the four numbers into four different columns.

Things I've tried with some success:

Using the "Right" function to break off the location code off the end of the entire name, and then using the ".parse" command in VB to seperate the numbers at given intervals.

This is great for certain purposes however because the four numbers are not always 2 digits, and the ".parse" command cuts at given intervals it is hard to accurately parse an entire set of data.

Some examples of location Codes are given below:

9-23-78-11
WEST DOE 11-16-81-15
KNOPCIK 3-9-73-9

View 9 Replies View Related

Extracting Data After A Dash

Nov 9, 2011

[URL]...

This entire link is in one cell i would like the data Iowa_Health_System

What formula can i use

View 2 Replies View Related

Can't Get Rid Of Dash In Blank Cells

Feb 12, 2014

I have a spreadsheet that shows a dash in some blank cells and I can't get rid of it. Cells are formatted as general, there's no event code, no conditional formatting, no data validation, no auto-formatting, no nothing. I can copy a cell from outside the range with no dash in it and paste it into one of the cells with the dash and the dash remains. I can't figure it out.

View 9 Replies View Related

Double Dash In Formulas

Sep 1, 2006

I recently saw Krishnakumar use the formula.
= sumproduct(--(a2:a10=d2),--(b2:b10=e2),c2:c10)

I haven't seen sumproduct used this way. Very cool. I see how it is working, but can anyone tell me more about the syntax of the dash dash used in array 1 and array 2? I have never seen this. How is it different than =SUMPRODUCT(1*(A1:A9=G1),1*(B1:B9=H1),C1:C9), which is the syntax I'm used to seeing.

View 9 Replies View Related

Clearing Entries By Dash Numbers

Feb 26, 2009

Here is what my worksheets looks like.

54841
54841-1
54841-2
54841-3
55654
58850
58850-1
53254
53254-1
53254-2
56696
56696-3
56696-4
53214
54789

So this list of information needs to be changed to the following

54841
54841-1
54841-2
54841-3
55654
58850
58850-1
53254
53254-1
53254-2
56696
56696-3
56696-4
53214
54789

All the numbers in red need to be cleared. No I have been able to find the original number and clear it. For example I can have the program find 53254 and clear it, but I am really having a tough time having it find -1, -2, -3 and so on. If the program finds a -1, I need any dashes after that to be cleared. If the program doesn't find a -1, then it goes to -2. If it finds a -2 then any number after that needs to be cleared. Here is the code I have to clear some of the numbers.

View 4 Replies View Related

Remove Space After Dash In Excel VBA

Feb 24, 2012

I have some rows that have some text for example "0- 56933 tex". the numbers are not always the same, but there is a dash, and sometimes there is no space after the dash. I want get the numbers before the dash in a variable and the one after in another variable.

View 9 Replies View Related

Removing Dashes Deletes Everything After First Dash

Mar 25, 2013

I have entries that look like this - 300-7188258-1

300-8983980-5

I need to remove the dashes. I find and replace all - with nothing. that has worked for YEARS. Seems simple to me.

Today, when I do it, Excel leaves the "300" and removes everything else. I even did a CLEAN function and it still does it.

View 3 Replies View Related

How To Remove Dash Out Of First Two Letters Of A Sentence

Jun 12, 2013

How to remove a "-" out of first two letters of a sentence. if there is a "-"

for example:

BEFORE: Red-Blue Shirt With Ribbon
AFTER: Red Blue Shirt With Ribbon

However, if there is not a "-" in the first two letters then it not change anything.

For example --nothing changes:

BEFORE: Red Blue Shirt With Ribbon
AFTER: Red Blue Shirt With Ribbon

View 8 Replies View Related

Formula- To Isolate Everthing After The Last Dash

Dec 24, 2009

i am trying to isolate everthing after the last dash, now i did the same with the first dash using left, i tried to do the same using Right to get "155", but it gives me incorrect result,

Sheet1  ABC887878-1554-155 87878987878-1554-155 554-155Spreadsheet FormulasCellFormulaC8=LEFT(A8,FIND("-",A8)-1)C9=RIGHT(A9,FIND("-",A9)+1) Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Accounting Prepayment Schedule

Jan 28, 2010

I'm trying to automate the calculation of my prepayment schedule. The linked image http://img402.imageshack.us/img402/2...mentqueryp.jpg shows the basic layout of how it will look. The yellow cells are going to be the only input cells but i'm unsure what formula will achieve the desired result i'm looking for across the remainder of the spreadsheet. Essentially I need the formula to look at the period (start and end dates) the invoice covers and apportion it correctly. The apportionment isn't a straight equal division per month though as it has to be calculated according to what element of the expenditure hasn't been realised yet.

For example in the car park rent line the figure of £8,000 in July is derived from the fact that that is how much hasn't effectively been incurred yet as it relates to the remainder of the invoiced period (Aug - Mar) and is calculated as 8/12 x £12000.

I hope you understand what i'm requesting, if not, let me know and i'll try to provide a better explanation.

View 7 Replies View Related

Copy Row - Accounting Monopoly

May 20, 2014

I am trying to make an accounting spreadsheet to be used with the game monopoly for my business students.

Each row I want to copy to a separate sheet within Excel.

For instance the row with the account title cash I want that to copy entire row to the cash worksheet. "go" revenue to the "go"revenue worksheet etc.

Date
Account
Debit
Credit

May 20
Cash
200

[Code] .......

Here are the sheets I set up to transfer to depending on the account title:

Cash

Properties

Acct.Pay. Monopoly Loan

"Go" Revenue

Rent Revenue

Misc. Revenue

Rent Expense

Misc. Expense

View 4 Replies View Related

Create A Simple Dash Board Using Only Excel?

Dec 17, 2013

I am trying to create a simple dash board using only Excel and no VBA. What I am trying to do is I want to create dependent drop downs and link up drops downs that will pull out the required data individually or with combination of those two. For example, let's say

A column has Dates of transaction,

B column has Item name

C column has Units

D column has Month name

E column has Shifts (Morning or Evening)

I am trying to make a dash board that will pull total Sum of individual items based on

Only Date wise
Datewise + shift wise
Month wise
monthwise + shift wise

I managed to create drop down list for dates, months, and shifts but i dont know the way how to link the individual drop down lists together to pull the data based on the selection. which function i should use here and how?

View 5 Replies View Related

Ignore Dash Sign In Formula Subtotal

Jan 26, 2014

I want to know how many emails are in my list. There is dash sign where the emal id is not available. The subtotal formula gives me the total entries in range but I want to know only email count numbers.

In my range there are 6 entries and only 4 email ids. Now I'm getting output 6 but I want it 4 ignoring 2 dash signs.

View 10 Replies View Related

MID Formula: Extract Only The Text Between The 1st And 2nd Dash In A Cell

May 27, 2009

I need a formula that will extract only the text between the 1st and 2nd dash in a cell. Example:

10466193-1909457-
10648145-3026718-t526363536m

This formula would return only the following:

1909457
3026718

View 4 Replies View Related

Removing Dash From Text Number String?

Oct 3, 2010

We often get spreadsheets from our customer that are formatted with both comma and dash combinations. We would like to only have the comma seperation.

Example:
R1, R2, R3-R5, R30

Result:
R1, R2, R3, R4, R5, R30

There is usually only one alpha character but sometimes more. Example: CR1, CR2, CR3-CR5.

The following macro works great if there are no alpha characters. how to solve the alpha/numeric combination?

Function Nums(rng As Range) As String
Dim adnum As Integer, n As Integer, num, Txt As String
num = Split(rng, ",")

[Code]....

View 9 Replies View Related

Replace Space With Dash In Text String?

Aug 7, 2013

I am looking to replace the space in between numbers and letters in a text string. The number of entries varies with each row.

Example of the entries in the column I need to reformat. Each entry is in a new row.

Code:
10 SSS
24 MNL
17 HLG 18 LTN
17 CBG 17 HLG 15 HIL 15 thn 22 ALV
17 ELP 34 HLG 15 HIL18 THN 10 TTL

What I am looking to achieve:

Code:
10-SSS
24-MNL
17-HLG 18-LTN
17-CBG 17-HLG 15-HIL 15-thn 22-ALV

17-ELP 34-HLG 15-HIL 18-THN 10-TTL

View 3 Replies View Related

Auto Numbering With Mark (Dash) Combination

Apr 6, 2014

How to make auto numbering with mark "-" combination and result placing in D column, like this below ;

row/line
col.c
col.d
col.e

15
john
-
xxxx

[Code] ........

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved