Selecting blank cells
Happen that I am trying to set a macro where I have to go to the last row
with information, then select "A-last" & "B-last" to the A1:B1, I did this:
But I do not know how to move to select A:B from the last row with
Any help would be appreciated.
Dim LastRow as long
'use column C to get the lastrow
lastrow = .cells(.rows.count,"C").end(xlup).row
'What did you want selected???
.range("a1:a" & lastrow).select
...Converting Lead Notes and Activies information into Opportunity re
The Lead Activities (phone calls, emails, task, appointments, fax, letter)
and the Notes created while working on a Lead do not "convert" into the
Opportunity. To get this information, you have to go to Closed Leads or
drill down through the Originating Lead link. I've read that this will be in
2.0 but in the meantime, can anyone suggest a simple workaround. I would
also like to hear about the best way to do this, simple or note. If I create
new fields in the Lead form and map them to new fields in the opportunity
form, the activity functionality is reduced to that of ...Stop spilling to next cell without Word Wrap
I'm trying to stop the text I type into a cell from spilling over into the
next cell without using Word Wrap.
Searching through past messages I found that someone suggested putting =""
in the next cell to the right to make it look empty.
I was wondering if there was a way that would make it so I didn't have to
remember to type something in the next cell over.
You could use event code to enter the space into the adjacent cell.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in any cell in Col A
On Error GoTo en...DO we need to format the path?
I am writing a macro in which I am passing the name of a file to a
If I pass just the name of the file everything goes fine, but if i give
the ull path, it shows me error.
pass("temp.xls") - > works fine
But pass("c:\temp.xls") -> shows error :"Run time error 9, Subscript
out of range"
DO we need to format the path in some special way or ???
JAtz_DA_WAY's Profile: http://www.excelforum.com/member.php?action=getinfo&...high light the selected cell
I have a very big spread sheet. Each row goes across 15 cells. How can I have
the row outline in a different color to show what row i'm on and once I tab
to the cell I need how do I get the colunm outlined in a different color
Try Chip Pearson's customizable RowLiner add-in for more positve viewing of
Note: does not work on a protected sheet.
Gord Dibben MS Excel MVP
On Tue, 8 Sep 2009 14:39:25 -0700, Frank <Frank@discussions.microsoft.com>
>I have a very big spread sheet. Each row goes across 15 cells....Diagram show blank as zero value when based on a formula
When a chart is based on a formula e.g. =IF(A1=0;"";A1*B1) then the chart
will show the blank value "" as zero even if you whant to plot gaps or plot
the empty cell interpolated. Yes, I know Excel say empty cell and the cell is
not empty when there is a formula. Is there anybody who have found a "work
around"? I often have formulas that generate zero in the result but I want to
plot the result as if it was empty.
I found the reply myself in a question from another user similar to this issue.
I will use NA() instead of ""
Thank you that works!
&q...Link cells with text not numbers
I'm working with a worksheet of cells with general information (text) that I
linked to various cells on worksheets within a workbook. I created the
formula below and it works but I'm not sure it is the most accurate way to
write the formula.
=IF('General Info'!C2>0,'General Info'!C2,"")
All linked cells contain text, not numbers, so I'm questioning the >0. Any
Although Excel sees a blank cell as zero and you will probably get away with
your method, I think you would be safer with.
=IF('General Info'...formating multiple excel .csv files
I've a folder with about 100 subfolders all containing
around 10 .csv files. I need to format all these files
using the same formatting (remove some lines, distribute
some text to differnt columns etc) Question:
Is there any way I can for example set up a macro to do the
formatting I need but then automatically make it run
through all the csv file in the folder structure ?
I look forwart to any responses and appreciate any advice
on the topic.
Your question is so vague that only outline answer possible which is
Yes. I would suggest you have standard names for...Output report to Word
I have a series of reports that I output to a Word doc in rtf. Is there a
way to keep the report format in tact? I.e., can I output the report so that
it looks the same as it does in the report viewer?
Nope. Not from what I know. One of the frustration of the Export to Word
function, the loss of formatting.
The solution is to print it as a PDF for distribution or automate the
creation of a word document from within access to build the formatting as you
require using vba.
Hope this helps,
> I have a series of reports that I o...Is Auto Expansion (i.e., wrap text) of a cell possible when the cell's contents are based on another cell?
I am trying to display the results from one tab (Tab 1) in another
So, for example, tab 2 contains the formula "=Tab1!A2".
However, when the results are too large to fit the cell in tab 2
(i.e., the cell that contains a formula that draws from a cell in tab
1), the wrap text feature does not work unless i first double click in
the cell in tab 2.
Is there any way around this? Can the wrap text feature work
automatically somehow? Or will i need to double click in every cell
that contains text that doesn't fit into the cell.
Thanks for any suggestions, or VBA code, th...date format problem #7
I have a csv file with date column in the "01/05/1990" format. I could use
Notepad and open the file with the correct date displayed ok.
When I double click the file and Excel opens it, the formats becomes very
some are right justified with "-" as delimiter e.g. "01-05-90"
some are left justified with "/" as delimiter e.g. "01/19/1990"
I tried to fomat the column to a common date format like "01-Mar-1990" etc
but Excel refuses to change.
First check those / dates. I suspect that they are t...Lead to Product and Lead to Competitor relationships
While browsing MS CRM logical DB Diagrams (http://www.microsoft.com/
dynamics/crm/using/sdk/DBDiagrams.mspx) for Sales Entities, I have
found many-to-many relationship between Lead and Competitor entities.
In SQL Server I made a diagram with the following tables: LeadBase,
ProductBase and CompetitorBase and then I added tables
LeadCompetitors, LeadProduct (these tables contain no data in my
case). You can see my diagram below. I saw many-to-many relationships
between Lead & Product and Lead & Competitor. As far as I understand,
these relationships must support functionality for adding ...VBA Conditional Format
I cannot get why this is giving me the "object doesn't support this property
or method" message at the '.Font.ColorIndex = 10' line. What's the deal? I
recorded a macro that did the same thing and the code is pretty much the
Dim h As Range
Set h = ActiveSheet.Range("D11, D12, D14, D19, D20, D22, K11, K12, K14,
.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=B11"
.Selection.FormatConditions(1).Font....how to Sum last 10 values of a dynamic column excluding zero
hi, i have this formula =SUM(OFFSET(P2,MATCH(1E+30,P:P)-1,0,-10,1)) which
sums the last ten values in column P, i was hoping someone could amend it
slightly for me. As column P is dependent on other entries the entire column
has 0 in it which makes this formula ineffective, i can't put an IF formula
into column P because then that wrecks other formulas that need column P, so
i need to work in a way of excluding zero into the forumla above.
Also, the formula does not work until there are ten values in the column, is
there any way to make it work and count the values up until ...Automatically format
I have a workbook for personal banking purposes. When I log on to my bank, I
copy the latest lines of my statement and paste into my workbook.
I use Paste Special | Unicode text and my columns are filled appropriately.
The only problem is that the last column, i.e. the balance, does not format
to two decimal places as I had already set up.
How can I automatically paste in the right format, please? Do I need a
After pasting your data:
Select the entire column (click the column heading)
Find the button on the "Home" tab (xl2007) or on a toolbar (xl2003) that looks somew...Keeping one of 2 cells always zero
Hi all of you,
I have such a requirement. There are 2 cells in which the user ca
input values. However, one of the two cells must always be zero fo
valid inputs. Is there any way to do this?
I was thinking of setting other cell to zero when user enters value i
one cell or prohibit user from entering value in a cell till the othe
is made zero but I don't seem to figure out how to do this.
Is a macro the only way out (trapping a worksheet change?)? Is i
possible to use someother functionality like data validation. I woul
also like to impose restrictions on the non-zero cell of values betw...Seed date formats to different year in different cells
Workbook was created in Excel 2003 (PC) and forwarded to another person who
opened it with another PC using either Excel 2000 or 2003 (unconfirmed which
version). The dates showed up as about 4 years off. My first thought? Maybe
the machine was set with a different system clock. But here is where it gets
the receipient returned the workbook via email, and the wrong dates still
show up even on the originator's PC. When he enters new dates, they show up
as correct, but the cells that had the original dates (or anywhere they are
cut/paste from those original cells, includi...CSV File
I am opening a common delimted file in Excel and formating 2 fields as "text"
to retain leading zeros. They appear correctly in Excel. I then need to
save as a "csv" file and retain the leading zeros. How can I do this? The
zero's always drop off in the csv file when retrieved again in excel.
do you need to save the csv every time, if you save I agree it will remove
There is no perfect solution to this but if your file format is standard,
you may consider preparing a excel template with the repeate, concencanate
and left and right formul...how do I convert office publication to word format
I'm trying to E-mail my publication home so I can print on my home printer
Convert it to pdf format.. this is a useful page for info on doing the
MVP - Windows Shell/User
"sedonasdad" <firstname.lastname@example.org> wrote in message
> I'm trying to E-mail my publication home so I can print on my home printer
You do realize that when you save your .pub file as a Word doc, you will lose
your graphics and fo...I need to F9 one cell in excel not entire worksheet
I have a very complicated worksheet. So when I change numbers it takes so
long for this one cell to recalculate. I need a F9 key for one specific cell
(Like word to update) But all I can find is entire worksheet or workbooks.
AFAIK this is not possible
"Paula" <Paula@discussions.microsoft.com> schrieb im Newsbeitrag
> I have a very complicated worksheet. So when I change numbers it
> long for this one cell to recalculate. I need a F9 key f...My Custom Line-Column 2 axes Chart dsn't work with +6sets of cells
I created a custom Line-Column 2 axes chart with 6 columns and it worked
fine, showing two different Y axis. Then I tried to do the exact same process
with a larger data set, with 10 rows again, but this time 12 columns, and it
doensn't work - it repeats the 12 columns twice, instead of recognizing that
I want the 2 sets of 12 on top of one another, like 2 layers, each with a
different Y axis. Any ideas on how to avoid this? I think it may be a flaw in
the software because, literally, when I delete half the cells and run the
same process, it works... it just doen'st seem t o kno...Fill leading zeros
I have a SELECT statement that I want to fill leading zeros in one column and
trailing blanks in another column up to xx characters. For example
SELECT CAST(EmployeeNumber AS varchar(8))
If the EmployeeNumber is 1234 I want to output 00001234
SELECT CAST(LastName AS varchar(15))
If the LastName is SMITH I want to output SMITHxxxxxxxxxx where x = space
Could this be done by casting a CHAR(15)?
> SELECT CAST(EmployeeNumber AS varchar(8))
> If the EmployeeNumber is 1234 I want to output 00001234
SELECT RIGHT('00000000' + CAS...Generate Leads/Accounts/Cases from a public website?
My employer asked me to investigate using a public .Net website to help
generate information to be used in CRM. Specifically, he'd like our website
to have webforms that, when filled out and submitted, would generate leads,
accounts, or cases depending on what our needs are (I suspect generating
leads would suffice for now).
Can anyone point me in the right direction on how to accomplish this? I've
found it difficult to find information online concerning this.
We're currently using CRM 3.0 although I'd be interested to know what
differences would be in...Formating Cells In 2003
I have a strange question and am looking for some help to answer this.
I have two machines, both running Office 2003 SP3. On one machine when I
format a cell to either Numbers or Currency I get the display to show
brackets () however on the other machine there are no Brackets shown. Both
Office versions are exactly the same, both machines are running XP SP3.
This is causing problems as on both machines worksheets need to be created
and when the one formats the cells they do not get the brackets which are
Can anyone shed some light on this for me?
Chee...Date format in the footer
Can one change the date format in the footer in Excel?
Only if you use VBA to load the footer.
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"JohnH" <email@example.com> wrote in message
> Can one change the date format in the footer in Excel?