Excel Macro VBA Tip 5 - Learn How to Input Values, Text, Numbers, and Formulas into Cells in Excel

Hi

I have received a formula something like =sumproduct(C1:C10,--((B1:B10)=D1),--((A1:A10)=E1)) as an example. I know how to use this formula as I was using in different ways to learn it.

Now, I like to study these kind of formulas and further I like to know how this formula works. Here in the above example, I dont understand why we use this symbol "--"?

Can anyone help me to find a way to study about these advanced techniques?

Dear Excel Users

I am trying to write a VBA macro, which can create a worksheet with content. When this macro has been executed one time it won't be used anymore. The resulting worksheet should work on its own.

I am unsure how to create formulas in the worksheet. Let's assume I wanted to place the formula "sum(A1:A5) in cell B4. Then I would just write the following in a code line:

Cells(4,2) = "=sum(A1:A5)"

The formulas are however not fixed as in the above example, but depends on user input. Suppose the user has delivered information about what cells to sum and what place to put that formula. Let's say I have defined two variables FR and FC containing the row and the column of the cell to place the formula, and suppose I have defined variables FromR, FromC, ToR and ToC specifying the range to sum. How do I write a code line to place the userspecified formula in the userspecified cell? I mean I can't just replace A1 and A5 with the userspecified range ...

I hope someone can help solving this problem. I have considered the possibility of using names for ranges.

Regards,

Erik

Hi

I realy breaking my head about this problem.
I need to make a userform, that makes the next things:
1.) for a entried a number in a text box,
2.) when i click ok, it should open a next form, with so many text boxes i have declared in the first text box
3.) these new text boxes, need to be related to cells in excel, for example when i entrie in the first text box a number, it should be input in cell A2, and so on...

thanks

1. Select View -> New Window (in Window Group).
2. Select Arrange -> Horizontal.
3. Select a cell and Select Formulas -> Show Formulas (in Formula Auditing Group).

To move between windows, press Ctrl+Tab or Ctrl+F6.

Hi

I am wondering if there is a simple macro that would allow me to format cells throughout a workbook based on whether they contain an input (ie number/text etc) vs. a calculation (anything with a formula).

Basically I'm looking for an automated way to colour my inputs in blue font and calculations in blank font across all worksheets in a workbook.

Any ideas would be great.

Thanks

Random cells are changing from formulas to unformatted values with no apparent reason!! These cells need to by formulas not values!!

Does anyone have any explanation for this and a way to solve it?

I have workbook with a lot of data on a worksheet, and a "dashboard" front worksheet that basically displays all the data that's on the other worksheet, but in a much easier to read format.
The raw data is actually written from our ERP program, and some of the data has cells with combined text and numbers. I'd like to display some of the data on these cells in the dashboard worksheet, but I can't find a way to do this.

I attached a sample of the data I would like to work with. This column has hundreds of entries and would be very cumbersome to edit them all by hand. I want to remove the "lb" from the cells in this column. Is there a way to batch remove just the text from the cells and leave the numbers, or is there a formula function that will remove the text and just work with the numbers?

I'm alittle confused about which version of excel i should get. I'm running Office XP and when I go to Help => About Excel => Excel 2002.

Should I be getting i.e. Excel 2002 Bible? or Would Excel 2007 Bible work the same? Also, what's a good book to start off to learn Excel macro programming?

TIA

I am helping a co-worker design some reports in Excel and I noticed a very strange problem.

When I modify formulas and hit "enter" the cell format is turned to text (making the formula useless) and I cannot change it back without using "Text-to-Columns".

Example, I made a vlookup formula that was referencing another workbook, and then noticed that I returned the wrong column #. When I went to edit the formula and change the reference column from 5 to 4 the cell became text when I was finished. This happens all the time to her, she just thought it was normal for Excel.

Anyone know what the problem is?

 To protect a cell containing any type of data, two conditions must be met:
Condition 1: The cell is locked:
1. Select a cell in the sheet and press Ctrl+1.
2. In the Format Cells dialog box, select the Protection tab.
3. Select the Locked checkbox and click OK.
Or
Select Home - Format (in Cells Group) - Lock.

Condition 2: The sheet is protected:
1. Select Review - Protect Sheet (in Changes Group).
Or
Right click the sheet tab and select Protect Sheet.
2. Click OK.
Protecting cells containing Text or Formulas, requires isolating the cells containing the type of different data from the rest of the cells in the sheet, locking them, and then protecting the sheet.
 To select and protect different type of data:
Step 1: Change the lock to unlock of all the cells in the sheet:
1. Select all cells in the sheet by pressing Ctrl+A, or press Ctrl+A+A from a cell in the Current Region/List range.
2. Select Home - Format (in Cells Group) - Lock.
Step 2: Selecting cells containing text or formulas:
Select Home - Find & Select (in Editing Group) - Constants or Formulas.
Or
1. Press F5.
2. Click Special, and then select Constants (for text) or Formulas.
3. Click OK.
Step 3: Locking cells containing text or formulas:
Select Home - Format (in Cells Group) - Lock.
Or
1. Press Ctrl+1.
2. Select the Protection tab, and then select the Locked checkbox.
3. Click OK.
Step 4: Protecting the sheet:
1. Select Review - Protect Sheet (in Changes Group).
Or
Right click the sheet tab and select Protect Sheet.
2. Enter a password and click OK.