Office Forum - Answered Threads, Microsoft Office Excel

Blank cell being seen as "0" and calculating

Wed, 16 May 2012 15:33:15 Z

In using the formula =--(1*D10=0%) to reflect a return of 100% to objective for a "0" obtained actual against a ")" objective.

Until the "0" is actually entered in the cell for the actual obtained, there is a blank cell.

How can I make sure the blank cell reference does not calculate as 100% rather only when I add the "0", if that is what they received for that month?

The above formula is reading the blank cell as a "0" and returning a 100%. How can I stop the blank cell from being seen as a "0" and further calculating on cells that I have not gotten to yet to enter data?

I have 2 cells, but I only want one to display at a time.

Wed, 16 May 2012 15:24:11 Z

I am asking the question: -

1) What commission rate is being charged?

2) Are you being charged a fixed fee?

There is an answer box for each, but only 1 box should be populated (if they place answers in box boxes, it will provide the wrong answer).

How do I set a formula that deletes the entry in the other box, or ensures that only 1 box can be populated?

Finding Percentiles

Wed, 16 May 2012 15:07:45 Z

Hello,

I'm trying to find the 20th and 80th percentile of an array of data.

This chart is showing the counts for a spatial analysis, so ultimately the percentiles will be associated to the total counts for each objectID rather than the specific values of each column. So if the total of row 2 (ObjectID - 12) is 50000 then my percentile would be of that number.

Now what I want to do is get the 20th and 80th percentile for row 2 and 3 (There are many more rows in the actual data set) )but instead of it giving me the value of the percentile, I need the column heading (1, 2, 3, 4, 5) that the percentile is within to be shown. It would be calculated in the column after total.

Example If row one has a 20th percentile of 24000 the value calculted would come back as 2.

ObjectID | 1 | 2 | 3 | 4 | 5 | Total |

12 | 23456 | 2345 | 34523 | 3454 | 3452 | SUM |

13 | 24532 | 9856 | 12342 | 5674 | 1234 | SUM |

Any Ideas?

How do I show a minimum value of 599 in an Excel cell when the formula calculation equals less than 599

Wed, 16 May 2012 15:03:37 Z

I am trying to set a formula that will default to 599 if the answer is less than 599.

i.e.

Estate Agency commission is 1/2% on completion, but subject to a minimum of £599

Can I create a formula that will reflect a 0 actual against a 0 objebctive as 100% to objective?

Wed, 16 May 2012 14:31:33 Z

On the first three months of the year my staff has been given a "0" Objective to meet on sales in a specific area. If they obtain a "0" as an actual, how can I make sure the % to objective is reflected as 100%?

Conditional format in column A based on the value in column B.

Wed, 16 May 2012 14:28:47 Z

Hello,

I think this might be quite easy!

Id like to conditional format rows in column A based on the text in the corresponding row in coulmn B. For example if B is yes the highlight the same row in A. So John and Dave would be highlighted.

John Yes

Mike No

Dave Yes

Ed No

many thanks

vba sorting by date

Wed, 16 May 2012 14:22:22 Z

I have a date column U13:U33

and an event column W13:W33

I would like some code that can sort by earliest date to latest date but not reference the sheet, just the range.

Thanks

SUMIF and Dates

Wed, 16 May 2012 14:08:07 Z

Please assume that my spreadsheet looks as follows:

A	B	C
1	203	01/12/12	01/31/12
2	14	02/01/12
3	10
4	77	02/24/12
5	-5	03/01/12
6
7	86

I am trying to write a formula in cell A7 using SUMIF. I want to sum the values in column A if there is a value in column B but not a value in column C. I have tried using ISBLANK but that doesn't work. I know this is simple but I am frustrated.

Formatting cells with SelectionChange

Wed, 16 May 2012 13:07:07 Z

I have the following code that works when I click in a cell. My question is, how can I make it so when I click back out of that cell the formatting changes back?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target.Cells(1, 1), Sheet6.Range("Jan")) Is Nothing Then
                With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16737792
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    End If
End Sub

.xlsm macro code stopped working

Wed, 16 May 2012 11:08:43 Z

I have an .xlsm file that all of a sudden stopped working.

The code is:

Private Sub Workbook_Open()

Set r = Sheets("ORDER").Range("J3")

r.Value = r.Value + 1

End Sub

It is suppose to change a number everytime the workbook is opened.

My cells do not add up: For example P11 has 8.00 and R11 has 48, the sum should be 384 but it comes up with 383.76. Any idea why and what I can do to stop this happening.

Wed, 16 May 2012 08:59:29 Z

1.89

0.00

0.38

2.27

2.84

0.00

5.16

0.00

8.00

383.76

5.73

Allow only one cell to be highlighted in stead of two

Wed, 16 May 2012 08:57:04 Z

When I highlight a single cell multiple cells light up. That means I deleat all or add to more than one place and formulas have more than one cell. How do I get it to light up only one?

Use VBA to delete sheets except defined sheets

Wed, 16 May 2012 04:46:49 Z

Dear Experts,

Some of the marcos will have "add sheet" feature.

May I know how to delete sheets except sheet1, sheet2 and sheet3.... the rest can be removed with one click ....

Thanks and regards,

Elton

Use VBA to Close all files

Wed, 16 May 2012 04:43:05 Z

Dear Expert,

Have learnt from the forum that open all files from a folder.

May I know how to close all outstanding Excel files except the file whom run this marco to close others?

Would the script to close without save and to close with save be very different?

Thanks so much for help.

Elton

How to convert limited text to excel spreadsheet

Wed, 16 May 2012 02:47:07 Z

I had to save an excel file as delimited text - now I want to convert it back to excel. I have done this in the past but can't remember how now.
Thank you

When I open an Excel file from Explorer by clicking on the file, two windows open: the spreadsheet AND a second Excel window, no spreadsheet open--just a blank Excel Window

Wed, 16 May 2012 01:13:08 Z

(Excel 2010, Windows 7, 64bit). When I open an existing Excel file from Explorer by clicking on the file, two windows open: the spreadsheet AND a second Microsoft Excel window (no spreadsheet open--just a blank Excel Window).

When I open the Excel program directly from within Excel, I get the single window (Book1). But opening Excel from a previously created file always gets me two windows.

modify conditional formatting formula with another variable

Tue, 15 May 2012 19:25:28 Z

=AND($N9=U$7,$N9=U$7)
=AND($O9=U$7,$O9=U$7)
=AND($N9<=U$7,$O9>=U$7)

u7 date
n9 date
o9 date

i9 date
j9 date

I had assistance with the above formulas a while back and now I'm trying to modify it to NOT to format if BOTH I9 and J9 are empty.

Thanks for your help

horizontally fill adjacent cells by double-clicking auto-increment handle?

Tue, 15 May 2012 19:21:23 Z

I have a row of data in (say) B2:Z2. I replace B2 with 102 and C2 with 103. If I select B2:C2, I have an auto-increment handle at the lower right corner. I thought that I could double-click this handle to fill the entire row with 102, 103, 104, ... At least, that's how it would work if the sequence ran vertically downward. Unfortunately, I haven't found a way to get this behaviour for horizontally exending the auto-increment for a very long row. *Is* this double-click filling only available for vertical auto-incrementation?

Time Format

Tue, 15 May 2012 19:16:56 Z

I am trying to format a time with the minutes first and then the hours so I created a custom format of mm hh. No matter what the minutes portion always shows up as 1. So for instance 8:15 shows up as 01 08 when it should be 15 08. I can work around it but I was just wondering if anyone had an expanation of why it is converting the minutes to 1.

ZIP CODES on existing mappoint 2009 map

Tue, 15 May 2012 18:18:26 Z

I created a County Map in MapPoint 2009. I know this program has access to all the zip codes in the counties. How can I get the Zip Codes to show on the map? Thanks

Darin

Macro Continues to Copy to Another range and delete dupes

Tue, 15 May 2012 18:13:46 Z

I use this line to copy data from one worksheet to another. Works great.

rng is a named range on another worksheet

Application.ScreenUpdating = False
    rng.Copy Destination:=Sheets("CreateChoiceSets").Range("M" & Rows.Count).End(xlUp).Offset(1)

I use this macro on multiple named ranges and they're always 2 columns of data, differing in the number of rows.

Now, what I need to do is ALSO paste the first column of this named range to A2 of this same worksheet, AND paste the second column of this named range to E2 of this same worksheet. And I need to remove any duplicates that I might have copied over. (The duplicates DO belong when I paste with the code above, but NOT in the step of pasting into A2 and E2.

An entire macro currently is as follows, and I have about 10 different ones, which I run based on the user's button-click.

Sub AddPreps()
Dim rng As Range
Set rng = Range("addprep")
    Application.ScreenUpdating = False
    rng.Copy Destination:=Sheets("CreateChoiceSets").Range("M" & Rows.Count).End(xlUp).Offset(1)
    Application.ScreenUpdating = True
End Sub

As usual, I will grant great favors in heaven to anyone who can help. :)

Thank you thank you thank you and I swear this project is almost finished!

combine formula

Tue, 15 May 2012 18:03:32 Z

Hello

I'm trying to combine 2 formulas into 1

I presently have extra columns in the woksheet and one column with the final result

Scenario

Column L Total Hours 174:00:00

Column M training 1:00

Column N consulting 2:00

Column O formula is column M less column N = 171:00:00

Column P time on phone 8:00:00

Column q available time = 13:00:00

Column R formula column P plus column Q 21:00:00

Colum S formula is column r/ column n - 12:28%

Is there a way to combine these 2 formulas into one.

I have tried several combinations but I get the wrong answer .

Thanks

Setting variable as "This Sheet"

Tue, 15 May 2012 17:54:10 Z

Hello all,

Firstly, I apologize. I am an extreme novice when it comes to VBA for excel. I am researching, and learning, but still have a long way to go. So please bear with me.

I am currently on a project, and have stumbled onto a small problem. I have a worksheet that will contain many sheets, easily over 100 when completed. The 1 sheet (Called “Roster”) contains a list of classes, and contains a list of employees. Next to each class is a button, this button take the user to a sheet that contains information about the class. On the sheet is a button that will take the user back to the Roster sheet.

My problem occurs with my personal OCD issues :P I cant stand the site of so many sheets cluttering the bottom of my worksheet. So to remedy this issue, I have made all the sheets other than Roster, not visible. The button that takes the user to the class sheets is as follows:

(TM81 is the “class code” for one of the classes. It’s a much shorter name than the actual class name. Each class has a unique “class code”)

Sheets("TM81").Visible = True

Sheets("TM81").Select

Sheets("Class Roster").Visible = False

This works fine, it takes the user to the class’ sheet, and makes the roster sheet not visible. Exactly what I want.

The problem arises in my button that returns the user to the Roster. I can make it so the Roster sheet becomes visible, and the user is taken there. I however don’t know the proper “verbage” to make the original sheet not visible. I can manually put each previous sheets name in the code to make it not visible again. This will be extremely tedious as there are potentially many, many classes. This is what I currently would have to do for every class page individually.

Sheets("Class Roster").Visible = True

Sheets("Class roster").Select

Sheets("TM81").Visible = False

I don’t want to have to type this for every class. My idea would be something as follows:

DIM CurrentSheet AS (im not sure what a “sheet” is stored in)

(I forget the proper code for the following, sorry)

Then set CurrentSheet as (Some kind of “This Sheet” function to set this variable to equal the sheet that the user is currently viewing)

Sheets(“Class Roster”).Visible = True

Sheets(“Class Roster”).Select

Sheets(The variable “CurrentSheet”).Visible = False

The idea here is to make a bit of code that I can set as the macro to a button on each of the class sheets, instead of having to write a new line for each one with that sheets name in it.

Thanks for reading, and any help available.

How can I add date to excel without using =Now()

Tue, 15 May 2012 15:40:50 Z

Whould like to have the current date inserted into cell E5 based on entering a value in G10.

Can this be done without me entering =NOW(). This causes the sheet to change dates when I reopen.

Private Sub Workbook_Open() '

     If (Range("g10").Value = "") Or (Len(Range("g10")) = "") Then
    Range("g10").Value = InputBox("Enter Unit Number: IE: 4369")
    Range("C16").Select

End If
End Sub

Don Murray

Sum formula that ignores blank cells when 1 or more of them would have been errors

Tue, 15 May 2012 15:30:36 Z

Hello.

I would like a sum formula that adds the values of 12 cells but ignores any of those that are 'blank' (forced to be blank per ISERROR formulae). I'd like the formula to add up, say, the 11 or 10 or even 5 non-blank ones...and the # of non-blank ones could vary from day to day.

Cell E5 is a formula. It returns a value if its predecessors contain certain values, else it returns a blank cell, and I want blank cells if either of 2 conditions is met: if the cell is for a future day, or if the formula would have returned a zero. This is good because I want my external line chart #s to NOT show a bunch of zeros for future days and to NOT spike up & down if a completed day truly had zero dollars (i.e., I want the chart line to stop & start as it moves right along the axis). This part of my formula works perfectly.

=IF($A5>'Budget'!$B$2,"",IF(ISERROR('Data'!I5),"",(('Data'!I5))))

The formula in Cell E5 is copied down the rows for each workday of the month.

Then, Cell EO5 is a formula.

=E5+N5+W5+AF5+AZ5+BI5+BR5+CA5+CJ5+CS5+DB5+DK5

The formula is copied down for each workday of the month.

The problem is that if E5 is blank, then EO5 returns an error #VALUE. If N5 is blank, same thing. If BR5 and DK5 are blank, same thing.

I would like a sum formula that ignores any and all 'blank' cells and adds up the rest of the cells. Hoping someone can help. Thank you.

Can I reference a Workbook scoped named range in the Worksheet Change code?

Tue, 15 May 2012 14:04:39 Z

I had three worksheets using the Worksheet_Change event to call a procedure in Module1. Everything worked fine.

Because users need a change on 1 of the 3 sheets, the Module1 code won't work for the new layout. I thought it would be easiest to just copy the code over to the Worksheet_Change area, modify it as needed and stop calling the Module1 code. The problem I ran into is when the Worksheet_Change code attempts to reference a Named Range.

Target.Offset(0, -2).Value = Range("TODAY").Value

Right click menu do not includes to start excel

Tue, 15 May 2012 13:44:07 Z

I suddenly found when I right click on any blank part in my desktop, there is [text]/[doc]/[ppt] and so on, but no longer has the [excel], but if I open an old excel or use start-programme-microsoft office-excel to start, they are works well...

Text to Date Conversion

Tue, 15 May 2012 13:11:01 Z

hi,

is there an easier way to convert text field like this into date for sorting? (20100104), I tried Datevalue function but its returning (#value). Also, tried to use =year()&month() but its returning(#num)

Thanks,

vba code to cancel code operation

Tue, 15 May 2012 12:55:03 Z

I have some code that runs how it's supposed to but I would like to have a button that I can use to cancel the operation. Right now I click "Esc" and then the vba box pops up and I click cancel. I would like for all of this to happen by just clicking a cancel button on my spreadsheet.

Thanks

Using password but with option of Read only

Tue, 15 May 2012 12:47:42 Z

In excel 2010, when I protect the document with a password it now doesn't have an option for people to open it as read only. It used to do this but for some reason it has stopped and just asks for a password with no other options.

How do I use excel cells in VBA to send email?

Tue, 15 May 2012 11:45:01 Z

This is the code I use to send an email from excel.

Is there a way to use info from the spreadsheet for the subject instead of typing it in.

Private Sub CommandButton1_Click()
Dim oOLook As Object
Dim oEMail As Object
'
Set oOLook = CreateObject("Outlook.Application")
oOLook.Session.Logon
Set oEMail = oOLook.CreateItem(0)
oEMail.Display

On Error Resume Next
With oEMail
.to = "P.O. Request"
' .CC = ""
' .BCC = ""
.Subject = "PO request"

.Body = "See P.O. Request as per attached."
' .Send
End With
' On Error GoTo 0
'
End Sub

Excel VBA Macro Required

Tue, 15 May 2012 10:40:39 Z

I have following data in sheet1 containing list of 06 observations (obs1 to Obs6) in Column A and list of samples (1 to 10) given in columns B to K. Only some observations relate to particulars samples. We validate all these observations to all samples and mark “Yes” in respective column, if observation exist in particular sample. I want to display results in Sheet2, observation wise list of only those samples that have been marked “Yes” against each observation. For example if samples 1,3,9 of obs1 have been marked yes. Then report should show Obs1 as heading and list of samples in separate rows.

Sheet1
Sample
Observations	1	2	3	4	5	6	7	8	9	10
Obs1	Yes	Yes	Yes
Obs2	Yes
Obs3	Yes
Obs4
Obs5
Obs6	Yes	Yes
Required Results in Sheet2
Obs1
1
2
3
Obs2
5
Obs3
8
Obs6
1
6

pasting Word table to Excel but all the columns were fit into one column

Tue, 15 May 2012 09:16:33 Z

i tried pasting the table to excel but all the columns were fit into one column........ i tried converting to text and webpage then importing into excel but the numbers were jumbled up e.g. 24 569 became 569 24.what to do?

Filter multiple values

Tue, 15 May 2012 08:36:19 Z

Hi all,

I have been scouring the internet for a specific answer for the last hour and a half now, and it's left me more frustrated than enlightened... I'm setting up a library system in Excel to be able to quickly filter specific details of a large array of advertisements, leaflets, brochures, etc and quickly find its location (also specified in the excel sheet.

A screenshot of how it looks:

Click here

Because of the fact this list will get pretty large pretty fast, I need a good system from the start. I want to be able to filter all columns, but the problem resides within the Product Group and the Products section.

I.e. #3 in the example screenshot.

This advertisement contains both PG3 and PG4, and contains three different products. Say that in three months I want to look for this advertisement using as a criteria PG4 and 903.001, I want #4 to appear as well. Simply put:

How do I get all Product Groups to appear in one filter, and how do I do the same with Products?

If there is anything else you need to know in order to be able to help me out, just ask away, and I'll do my best to answer! Thank you very much in advance.

If this is not the optimal way of doing it, I'm open for suggestions!

Regards,

Bassianius

In a table of occations I want to count the number of certain values the last 5 hours.

Tue, 15 May 2012 07:17:11 Z

See table below. I want to count the number of 1's in column AH the last 5 hour (backward in time from 15.05.2012 05:09 ), The formula should be placed in AH1 (The answer is 4).

A	B	……………….	AH
1	Time	Temp
2	14.05.2012 21:24	89,51	0
3	14.05.2012 21:26	89,93	0
4	14.05.2012 21:35	87,99	0
5	14.05.2012 21:38	88,42	0
6	14.05.2012 21:41	88,01	1
7	14.05.2012 21:49	88,43	0
8	14.05.2012 22:02	87,44	1
9	14.05.2012 22:11	88,02	1
10	14.05.2012 22:31	85,53	0
11	14.05.2012 22:45	85,94	1
12	14.05.2012 22:55	85,03	0
13	14.05.2012 23:25	86,65	1
14	14.05.2012 23:47	85,15	0
15	15.05.2012 00:30	86,29	0
16	15.05.2012 01:02	84,74	0
17	15.05.2012 01:43	86,23	1
18	15.05.2012 01:51	85,66	0
19	15.05.2012 02:10	86,73	0
20	15.05.2012 02:36	85,27	0
21	15.05.2012 02:45	86,11	0
22	15.05.2012 02:57	85,59	1
23	15.05.2012 03:05	86,01	1
24	15.05.2012 03:26	85,04	0
25	15.05.2012 03:35	85,53	0
26	15.05.2012 03:41	84,37	0
27	15.05.2012 03:56	85,48	0
28	15.05.2012 04:00	84,77	0
29	15.05.2012 04:04	85,19	0
30	15.05.2012 04:13	84,12	1
31	15.05.2012 04:18	84,6	0
32	15.05.2012 04:20	84,18	0
33	15.05.2012 04:38	85,16	0
34	15.05.2012 04:46	84,66	0
35	15.05.2012 04:54	85,57	0
36	15.05.2012 04:57	85,16	0
37	15.05.2012 05:07	85,85	0
38	15.05.2012 05:09	85,52	0

Thanks in advance

Per Morten Lefdal

How to Add TextBox To A Frame Via Code?

Tue, 15 May 2012 04:26:28 Z

What would be the correct version of the following attempt to create/add a textbox within a frame on a form, upon a click of Command Button?

Private Sub CommandButton1_Click()

Me.Frame1.AddTextBox

End Sub

Thanx in advance.

How do I set-up a dynamic filter in my macro

Tue, 15 May 2012 01:20:56 Z

I do weekly data dumps from our project database. This dump goes from col A to col KA and can be from 150 rows to over 2000 rows. No telling how many rows of data will come up.

I have recorded a macro doing all the formatting and hiding of non-useful information but I need to send this spreadsheet which now has 10 cols of data to other sales team members for comment so I recorded a macro to filter for project amounts not equal to zero in the column AK which is always the column I need. The macro lines relating to it are:

    Range("AK1").Select
    Rows("1:1").RowHeight = 43.5
    Columns("KC:KC").ColumnWidth = 12.29
    Range("AK1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$KC$154").AutoFilter Field:=37, Criteria1:=Array("<>0"), Operator:=xlFilterValues

I tried using the macro for more than 154 rows and it seems to work but am worried that at some stage it will encounter errors if I don't fix it or make it dynamic.

Thanks

MACRO MODIFICATION, onkey

Tue, 15 May 2012 00:50:32 Z

hi, i am trying to modify a macro from use of: hitting 1 key, to: 2 keys, to make a macro work. although i can have it perform any function wanted, i have included the home example at the bottom (lets home key go to more than 1 location in a line). thanks in advance.

Application.OnKey "{HOME}", "myHome" 'name myhome as desired

using example of: "ALT" and: "a" keys. STATUS: got just the "a" key to work by itself, but in trying to combine with ALT, there is a problem.

would seem i need some other way to define the object for the: ALT key

these do not seem to work:

    'Application.OnKey "{ALT}" And "{A}", "Cut1"    'failed
    'Application.OnKey "{ALT}" + "{A}", "Cut1"   'failed
    'Application.OnKey "{ALT+a}", "cut1"    'method of object failed
    'Application.OnKey "ALT+a", "cut1"      'failed
    'Application.OnKey "ALT", "cut1"      'failed
    'Application.OnKey "Alt", "cut1"      'failed

    'Application.OnKey "{ALT}", "myend"      'failed
    'Application.OnKey "{Alt}", "myend"      'failed
    'Application.OnKey "{a}", "cut1"      'progress, a works for "end", prob: Cut1 cannot be found, oops, not case sens macro or ltr A,
    Application.OnKey "{ALT+a}", "cut1"     'ohono, starting over (but cannot be found is after error), stuck: ALT+a does not work

========== ANSWER:

Application.OnKey "%{a}", "cut1" 'ANSWER for: cntrl alt f12: "^%{F12}" close all files & reopen

Application.OnKey "%3", "cut4" 'PROBLEM: numbers do not seem to work, ANSWER: rem brackets around 3.

PROBLEM: SHORT CUTS IN TOOLBARS FOR MACROS HAVE TO BE UNDONE: by removing ampersand infront of the intended ALT-shortcut. IS THERE A WAY TO MAKE LINKS WORK FROM the OnKey or some other work around ??

( a in brackets, or cut1 items are Not case sensitive)

========== extra info: (what using the home key to do: 2 home destinations instead of just one)

Sub myHome()    'Home key has 2 destinations automated
    Dim L2 As String
    L2 = Range("L2")
    Dim N8 As String
    N8 = Range("N8")

If ActiveCell.Column = Range(L2).Column Then   'manual: "DD:DD"
Cells(ActiveCell.Row, N8).Select        'manual: "DM"
Else
Cells(ActiveCell.Row, L2).Select        'manual: "DD"
End If

'L2 is a fixed/ permanent cell location that has a dynamic reference: (4 is row formula resides in: no $4 sign = DD:DD)
'=SUBSTITUTE(SUBSTITUTE(CELL("address",$DD4),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$DD4),"$",""),ROW(),"")
End Sub

========== END KEY MULTIPLE DESTINATIONS

Sub myend()    'insert in a standard module, part 2 of reassign End key has 3 destinations automated
    Dim L2 As String    'home    CT
    L2 = Range("L2")
    Dim L3 As String    'fm    DI
    L3 = Range("L3")
    Dim L4 As String    'to    DP
    L4 = Range("L4")

If ActiveCell.Column = Range(L3).Column Then
Cells(ActiveCell.Row, L4).Select
Else

If ActiveCell.Column = Range(L4).Column Then
Cells(ActiveCell.Row, L2).Select
Else

If ActiveCell.Column > Range(L3).Column Then 'os, works with 1 endif here
If ActiveCell.Column < Range(L4).Column Then
Cells(ActiveCell.Row, L4).Select
End If
Else

Cells(ActiveCell.Row, L3).Select
'End If 'extra
End If
End If
End If
'L2 is a fixed/ permanent cell location that has a dynamic reference: (2 is row formula resides in: no $2 sign = CT:CT)
'=SUBSTITUTE(SUBSTITUTE(CELL("address",$CT2),"$",""),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("address",$CT2),"$",""),ROW(),"")
End Sub

I have a column with positive and negative numbers. I want to separate them into two columns one negative numbers and one positive numbers. How do I do this?

Mon, 14 May 2012 23:26:27 Z

i have this

Amount

-20.00

54.09

40.83

41.58

41.33

42.83

i want this

Debit	Credit
-20.00
	54.09 40.83 41.58 41.33 42.83

linking sales information from ebay to excel

Mon, 14 May 2012 22:18:30 Z

I am unsure as to whether this is possible or not. I am looking to link the sales data from ebay to my excel spreadsheet. I would want to to obtain the item name, sales price, P&P cost, date sold, ebay fee, paypal fee and listing fee and enter in to a excel spreadsheet, is this at all possible or is it wishful thinking?