Free Microsoft Excel 2013 Quick Reference

Display contents of cell in macro

in macro have code:
01=Range("a3").value

What code will display value captured in o1?

cell a3 can contained "Product:" or number 4

thanks
rleonard


Post your answer or comment

comments powered by Disqus
How can I display the contents of a cell in another as part of a text
string.

Such as... "The answer is "B4""

where B4 is the contents of cell B4?

Thanks

How can I display the contents of a cell in another as part of a text
string.

Such as... "The answer is "B4""

where B4 is the contents of cell B4?

Thanks

Hello, before I begin creating something from scratch I was wondering if there is code available for the following concept:
Would like to have the ability to simulate a text box or even just a floating text that would appear when my cursor hovers over cells that have text in those cells. For example i have a large rectangular grid of cells that have clusters of cells in randomn locations throughout the grid that all have the same ID like "A-00" and "B-00". My goal is to have the user see what the text in the cell is on a larger scale than the fonts of the cells themselves just by moving the cursor around in the grid. The reason is that both my font size and font color makes the cell contents impossible to view given my field of view, which i cannot change. I would even settle for having a text box appear or just the text floating somewhere on the spreadsheet that would appear if I click in any of the cells in the grid and then disappears after i double click or something like that. Is this possible in VBA?

I cannot "BOLD" the contents of cells in my spreadsheet by clicking "B" on the menu bar. However I can do so by clicking on "Format - Cell - Font" and then using the selecting the Bold option. I wonder why this happens.

G'day,

Can I display the contents of a range of cells in a message box?

Cheers

MichaelH

How can I display the contents of a cell in another as part of a text
string.

Such as... "The answer is "B4""

where B4 is the contents of cell B4?

Thanks

Basically what i want to do is display a range of cells in the same workbook. Maybe create a dump sheet elsewhere in the book but link all the data to show on a Multipage control.

Where i am stuck is not knowing how to display the data ( also edit it).

Help appreciated.

Beginner self-studying excel user.

Hello,

I have a spreadsheet that has multiple rows and columns of data. Some of the columns are hidden. I want to "foolproof" the workbook and prevent the contents of cells from being deleted if that cell is in a hidden column. I think that this may not be an issue in earlier versions of Excel, but in Excel 2007 it is a problem.

For example:
Column B contains data. The user selects "A1:D1" and presses the delete key. Ideally, I want a prompt to appear asking the user, "Are you sure you want to delete the contents of these cells, including those that are hidden?". If the person chooses Yes, the contents of the cells are deleted. If the person chooses No, then the contents of all the cells are deleted, as would normally happen when the delete key was pressed.

I have tried to fix this with the "Application.OnKey" feature. Here is what I have so far:

Sub changeto_special_delete()

    Application.OnKey "{DELETE}", "special_delete"

End Sub
Sub special_delete()

    If Selection.Columns.Count > 1 Then 'If more than one column is selected, it may contain cells in hidden columns
        
        'Question box asking "Are you sure you want to delete the contents of these cells?"
        'If NO then exit sub, otherwise proceed to deleting as normal
    
    End If
    
    
    Selection.ClearContents
    '*****Instead of Selection.ClearContents, I want to put some code here that will tell Excel to do what it normally does
when the Delete key is pressed. If there is a way to do this, then I can get the code to work. Any ideas??
    
End Sub
I'd very much like to know how to do this.

Thanks!
BjornTown

I have a range in A1:B4 with data like shown below.

12	11
12A	11A
12B	11B
12C	11C
I want to to know the content of the cell in upper left corner if I range this area. If I range A2:B4 am I interested in the content of cell A2.

Any suggestion what I can do. A user defined function?

I have just begin to learn VBA for Excel and I need help.
Can anyone give me code that copy content of cell in one worksheet to
cell in other worksheet dependending of value in third cell. For example
how to copy content of desired range of cells at sheet2 to some range in
sheet3 depending of value of cell in sheet1 which is date.

Thanks.

I have just begin to learn VBA for Excel and I need help.
Can anyone give me code that copy content of cell in one worksheet to
cell in other worksheet dependending of value in third cell. For example
how to copy content of desired range of cells at sheet2 to some range in
sheet3 depending of value of cell in sheet1 which is date.

Thanks.

Does anyone know if I can display the contents of an array in a cell.

eg in simplest form the array formula in cell b1 is {=a1:a7)
the contents of these 7 cells are numbers 1 to 7

therefore array is {1;2;3;4;5;6;7)

How can i refer to cell b1 in another cell and get that cell to display the array as 1;2;3;4;5;6;7 (or preferably 1,2,3,4,5,6,7)

I need a formula as a solution (no help doing f2 then f9) and not a macro.

Can anyone help, is it poss?

Hi,

I am trying with out much sucess to write a macro which will rename a sheet with the contents of a cell in said sheet. I need to beable to do this on multiple sheets with a workbook.

Many thanks in advance for any help you can give

Jo

Question:

I have a column range of numbers.( c19 : c3353)

The cell address that contains the maximum value in that column of numbers is displayed in a particular cell c3355, found by this formula:

="C"&MATCH(MAX(C19:C3353),C19:C3353,0)

In cell below this, C3356, I want to display the contents of the cell listed as the result of this formula calculation.

How do I do that - display the contents of a cell by indirect reference ? ( other than doing the easy solution of just finding the MAX value again of that range)

I have created a spreadsheet that is used to create orders. Relatively unskilled users enter box level information into an entry sheet which generates a bill of materials (BOM). There are both one to one relationships and one to many relationships between the entry sheet and the BOM. Selecting any two specific items together may also alter the resulting BOM. The BOM contains about 100 distinct items. In general only a small percentage of the items in the BOM are actually used in a particular project.

I would like to implement a conditional formatting function something like
ˇ§if contents of cell = 0 then row height = 0ˇ¨
to hide the whole row.

This would result in a list of required items rather than a sparsely populated list of predominantly zero quantity items.

Data filtering is one solution, however, there are some problems.
„h The BOM is a protected sheet so data filters do not work while the protection is enabled.
„h Data filters do not update if the source data is change unless the filter is re-selected.

A macro that unprotects the BOM sheet, applies a data filter then resets the password is a possible solution but this would leave the spreadsheet password visible if the macro crashed. Also the user would need to run the macro each time an item is added. A macro triggered by select the BOM sheet would be acceptable but is this possible?

Is there a better way to achieve this result? If not I would appreciate some guidance on the code needed to achieve this objective.

Thanks David from NZ

Hi, i need a macro written to clear contents of cells with criteria.

I have data starting in row A3, across 62 columns, this may vary depending on which month we are in. For an example i have chosen the month of May which has 31 days.

In Col A3 onwards is a list of p’folios and in Col B3 onwards is values associated with each p’folio. Col A and B represent 01-May, Col C and D represent 02-May etc
I need a macro to clear cells which have portfolios starting with “ZI” and “FI”, I don’t want the rows deleted but want the rows to move up one cell when data is cleared, I need this for every column of data.

So let’s look at Col A for an example, we can see it has p’folios ZISZAE and FISAAE, therefore will clear cells A3 and B3 move A4 and B4 one cell up, and the same scenario for A5 and B6 clear the contents and move Cells A6 and B6 up. The same can be repeated for all other columns,

Col A Col B Col C
01-May-08 Amount 02-May-08 Amount
3 ZISZAE 12 AIMTES 76.98
4 OPTMOD 15.8 ZIZZAE 89
5 FISAAE 344.87 CAETRN 76

I am using Excel 2003 and wish to link the contents of a Word document into a cell. If I use hyperlink, I can click-through to the Word document
Is there a way of having the contents of the Word document automatically show in the cell without having to click the hyperlink?

Rgds, Tony

I used the SumIf function to display the contents of a field in Sheet 1, in
sheet 2. This worked great for numbers, however, i want to be able to search
for a specific date and display the contents of a cell (that is text), stored
in Sheet 1, in sheet 2.

The twist. I need to search, on SHeet 1, column B, for a particular date.
If that date exists, I need to search Column C, of sheet one, and display the
contents, in a cell on sheet 2.

How can i accomplish this?
--
Carlee

I need to set some conditional formatting in cells based on data from
different worksheets in the same spreadsheet. When I attempt this, I
get an error that conditional formatting can't use data from other
worksheets, so I'd like to display the contents of those cells in the
other spreadsheet on the spreadsheet that will have the condtional
formatting. Example = Cell A1 on spreadsheet A shows the data from cell
B2 on Spreadsheet B. Is this a formula I'd use, and, if so, which one?

--
telewats
------------------------------------------------------------------------
telewats's Profile: http://www.excelforum.com/member.php...o&userid=30270
View this thread: http://www.excelforum.com/showthread...hreadid=505375

I've very lillte or no experience of macros and got some very useful
help from a kind user of this forum a couple of days back and I'm back
looking for more help. Its a bit long winded but here goes.

I need a macro which checks the contents of a couple of ranges of
cells, starting with the first range of cells lets say it checks cells
A4,B4,C4,D4 down to cells A20,B20,C20,D20. Now if for example cells
A7,B7,C7 and D7 have values (note the formulas used in these cells
means that if A7 has a value then B7,C7 and D7 will also have a value
if A7 is blank then B7,C7, and D7 will be blank) and no other cells in
the range have values except A15,B15,C15 and D15 then the macro should
take the contents of cells A7,B7,C7 and D7 and assign them to the first
lot of cells in the range that are blank i.e. cells A4,B4,C4 and D4 and
take the contents of cells A15,B15,C15 and D15 and assign them to cells
A5,B5,C5 and D5 and so on.
It then moves on to the next range of cells and does the same thing. In
total there are about 12 different ranges but if I had the format of the
code for the first 1 or 2 then the rest will be nearly the same.
I'm not sure I made a very good job of explaining so I have attached a
file which shows what I have and what I want. Help would be greatly
appreciated.

--
adw223
------------------------------------------------------------------------
adw223's Profile: http://www.excelforum.com/member.php...o&userid=24750
View this thread: http://www.excelforum.com/showthread...hreadid=383902

Hi Guys,

Please I need a vba code to find Invoice No. in a database and replace content of a cell in the same row.

I have a Sales database with 7 columns, as follows:

1. Invoice No. - Column A
2. Invoice Date - Column B
3. Month of Sale - Column C
4. Gross Amount - Column D
5, Vat Amount - Column E
6. Net Amount - Column F
7. Date Paid - Column G

I have created a Multipage Excel Userform for - (1). Sales Data Entry; and (2).Receipts Entry.

I already have a vba code for the Sales Data Entry, and the code works fine. When an invoice is generated, the Sales Data Entry part of the Multipage Userform is used to enter the sales invoice details onto the database. At this stage, what is entered in the last column i.e. Column G is the word 'UNPAID'.

The second page of the Multipage Userform is to be used to enter Receipts and update the database. That page contains only two textboxes, namely - (1) Invoice No. and (2) Date Paid, and a command button named 'Enter Receipt'.

Please I need a macro to run the page 2 of multipage userform (i.e. the 'Receipts' page) - so that when a user enters the Invoice No. and Date Paid, and clicks the 'Enter Receipts' button, the macro will make a search in Column A of the database for the Invoice No. that has been paid. If the Invoice No. is found, then replace the word 'UNPAID' that is entered against the invoice no. in Column G with the Date Paid (in dd/mm/yyyy format).

I need to clarify that each Invoice No. is unique and is never repeated in Column A (i.e. an invoice number cannot appear more than once in column A). The vba code should also be able to provide a message saying 'Invoice No. not found' if the search does not find an invoice no. that is keyed into the 'Receipts' userform (may be in error).

Thanks in advance for your kind help.

Buddy

Can the value of a cell be displayed even if the cell is on too narrow a row?
I am designing sheet music staff on a spreadsheet in which the staff lines
alternate on thick and thin rows.
I cannot display the contents of a cell that is in a narrow row unless I
superimpose on it an autoshape containing an address pointing to that cell.
The problem with such solution is that the autoshape is visible all the time
even if the cell is empty.

Hey all,

I'm sorry if this question is confusing but I'll try to make it as clear as possible.
Say I have a cell filled with just text in G5, and in H5, I5, and J5 I have numerical values.

Now, in cell A5 I enter "=G5", which naturally displays the text content of cell G5.

What I am trying to get is that as soon as I do this, for cell B5 to display the contents of H5, for cell I5 to display the content of I5, and so on. I know I can achieve this by dragging the bottom right pointer of cell A5 over the specified areas, but I would rather have this happen automatically. Is there a way to do this?

Thank you for the help and sorry again for any confusion

I need to set some conditional formatting in cells based on data from different worksheets in the same spreadsheet. When I attempt this, I get an error that conditional formatting can't use data from other worksheets, so I'd like to display the contents of those cells in the other spreadsheet on the spreadsheet that will have the condtional formatting. Example = Cell A1 on spreadsheet A shows the data from cell B2 on Spreadsheet B. Is this a formula I'd use, and, if so, which one?


No luck finding an answer? You could always try Google.