Over 1 million tech questions and answers.

Excel 2003 macro: log contents of selected cell, clear cell

Q: Excel 2003 macro: log contents of selected cell, clear cell

I'm attempting to write my first macro for an Excel 2003 workbook. I'm not completely code illiterate (I've got moderate skills with AutoLISP), but I'm new to VBA and am not yet an Excel power user, so please be gentle.

The macro I want to write will:
check that the selected cell's content is underlined before proceeding
copy the content of the currently selected cell into an external plain text .log file
.log file lines should be: year/month/day - time - username - cell contents
.log file names will probably need to be generated
clear the cell's content and formatting (particularly underline and text/background color)
Here's what I have so far:
Code:
Sub Unpost()
If Selection.Font.Underline = True
Then Selection.ClearFormats And Selection.Clearcontents
Else
If MsgBox("The selected cell is not underlined...are you sure?", vbOkCancel) = vbOk
Then Selection.ClearFormats And Selection.Clearcontents
Else Exit Sub
End If
End If
End Sub
If I've written it correctly, it should currently do everything except log the cell contents. This, from what I've seen, is going to be the trickier part. I intend to use this macro 50+ times per weekday, so at some point the .log files will get too long to be useful, so I assume it will need to automatically create new logs (perhaps "year-month.log"). I've seen some useful info about appending to an external log here and here, but I'm not sure how to go about putting it into my code, and have no idea how to generate and name new log files.

Any help or ideas you could give would be greatly appreciated.

RELEVANCY SCORE 200
Preferred Solution: Excel 2003 macro: log contents of selected cell, clear cell

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel 2003 macro: log contents of selected cell, clear cell

You need to use the "File Scripting Object" to create and/or append text to a file. I've included a link below to get you started. If you are unable to figure it out on your own let me know and I'll write the code for you.

http://www.virtualsplat.com/tips/visual-basic-fso.asp

Rollin

Read other 1 answers
RELEVANCY SCORE 132.4

Hi All,

I need to make a macro that will add borders to certain cells.
I have a generated report from another product so I don't know how many rows there will be until the report is generated. The number of rows is specified inside the report, so I do have access to it. It is stored in cell C5.

So what I want to do is add borders around C5 number of rows starting at A7..E7

I tried using a rule, to just put borders around cells that contain data, but that won't work because sometimes cells are blank and I still want the border around them.

Help?! Please

Thanks a bunch!!!
Lori
 

A:Excel Macro to add borders based on cell contents?

Hi Lori

Here is a macro that will take the value in C5 e.g. 10 and then apply borders to all cells in A7 to E16
Code:
Sub add_borders()
lRow = Range("C5") + 6
Range("A7:E" & lRow).Select
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlC9ontinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
 

Read other 1 answers
RELEVANCY SCORE 130.8

Excel Marco Dropdown Menu Cell Contents I have an excel workbook with a "INPUT" WkSht, and a "ACTIVE" WkSht.
INPUT has a dropdown menu that looks up a list in ACTIVE, locates a desired value, and displays several cells contents in ACTIVE, - in the INPUT page.
This is working.

Question 1:
One cell in ACTIVE contains "comments" - how do I display the comments in the cell from ACTIVE, in a cell in INPUT - so I can look at all of them.

Question 2:
How do I update the contents of a cell in ACTIVE, at the same location that was selected from the dropdown.

I am a beginner - please help.

See present MACRO below...

Sub UpdateLogWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCopy2 As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D6,I18,D20,D19,D8,D10"
myCopy2 = "D5,D6,D7,D8,D9,D10"
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Payments")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yy"
End With
.Cells(nextRow, &q... Read more

A:Solved: Excel Macro Dropdown Menu Cell Contents

Read other 16 answers
RELEVANCY SCORE 129.2

Using Excel 2003 in Windows XP

I would like to use the contents of one cell as the destination location for copying data.
For example
I have 2 worksheets 1) Results and 2) info
in info
A1 = 'ABC'
C1 = 'Results!O54' < this is calculated based on other data in sheet.

Using a macro, I'd like to copy contents of A1 to cell location 'Results!O54' more specifically to where ever C1 points... C1 will change based on other data in info sheet.

The macro record for action looks like this (but I would like the 'O54' to be based on contents of C1 which changes)
Range("A1").Select
Selection.Copy
Sheets("Results").Select
Range("O54").Select
ActiveSheet.Paste
Sheets("info").Select

There is more to it then that but I think this is where I am stumped.
 

A:Solved: Excel: Uses contents of Cell to select a cell

Sheets("info").Range("A1").Copy Destination:=Sheets("Results").Range(Sheets("info").Range("C1").Value)
 

Read other 3 answers
RELEVANCY SCORE 129.2

Excel Masters, I am not sure how to loop through the following code for multiple row formatting. The code works fine if the user has selected cells within a single row. However, when multiple rows are selected, the 'With ActiveCell' formatting only works for the first row selected. How can I loop the 'else' statement to format the ranges for all rows with a cell selected?
Private Sub Button_DeleteRow_Click()
Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then

Selection.EntireRow.Delete

Else

Selection.EntireRow.Interior.ColorIndex = xlNone
With ActiveCell
Range(Cells(.Row, "AA"), Cells(.Row, "AN")).Interior.ColorIndex = 15
Range(Cells(.Row, "c"), Cells(.Row, "d")).Interior.ColorIndex = 15
Range(Cells(.Row, "a"), Cells(.Row, "a")).Interior.ColorIndex = 15
Range(Cells(.Row, "j"), Cells(.Row, "j")).Interior.ColorIndex = 15
Range(Cells(.Row, "n"), Cells(.Row, "n")).Interior.ColorIndex = 15
End With

End If

End Sub

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.
 

A:Solved: Excel Macro - Loop selected-cell formatting for multiple rows

Something like:

Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then

Selection.EntireRow.Delete

Else

Selection.EntireRow.Interior.ColorIndex = xlNone
SelRows = Selection.Resize(, 1).Cells.Count
Cells(ActiveCell.Row, 27).Resize(SelRows, 14).Interior.ColorIndex = 15

'(and so on)

End If

?

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.Click to expand...

If you mean "how do I bypass that for Selection.EntireRow.Delete then:

Application.EnableEvents = False
Selection.EntireRow.Delete
Application.EnableEvents = True
 

Read other 2 answers
RELEVANCY SCORE 128.4

Hi

I have a lot of cells in a worksheet that I would like to edit. The cells have this type of data 08:59:39 AM and I would like to change it to '08:59. When I record a macro it changes all the cells to the same value although they have different values. Please could you assist me.

Thanks
 

A:Edit cell contents in excel 2003

Read other 8 answers
RELEVANCY SCORE 127.2

I have an Excel 2003 question that puzzles me, and I'm hoping someone can help me out...

I have 4 columns of cells that that are 20 cells high with information in most of the cells. There is a border around each cell in the columns. My problem is, when I move one cell with info to another column, it will take the border with it, leaving the cell border-less, and I have to manually create a border around the cell again.

My question is, how can i cut/ move the information in the cell and leave the border intact, so I don't have to keep recreating a border for every moved cell.

Thanks,
Billie26
 

A:Excel 2003 - Move Cell Contents w/o Border

You could create a simple macro that Copies the cell contents and then "clears" the contents from the original cell and then Paste the contents in to the location.
 

Read other 2 answers
RELEVANCY SCORE 123.2

I have built a simple timesheet for someone in my dept, which includes amongst other things:

- hours worked over a month,
- how many hours owed/extra worked
- a cumulative of this figure over the months.

The requestor wants to be able to type a letter such as 'e' to represent a 07:30hr shift. So they type 'e' and the cell contents change to 07:30. There are about 6 different codes.

Normally a Vlookup would work EXCEPT you can't change the contents of the cell your are working upon.....cicrular referencing!

I could see two ways forward: either a function that allows the above

OR (perhaps more favourably)

leaving the lookups until the hour calculations at the base of the form. i.e Instead of a simple Sum for the hours worked that month, it could read the values in the range, look them up from a table(vlookup), and then sum the looked up figures to give the answer in the 'hours worked' cell.

I vaguely remember this being possible from my university days but can't remember how.

Can anyone help me?
 

A:Excel 2003: automatically changing a cell's contents based on keyword entered

Read other 9 answers
RELEVANCY SCORE 122.8

Hello

I need to do the following in VBA:

If cell in column C contains the value GBP, change cell S from that row from 13 to 23
or
If a cell column C contains the value USD change cell S from that row from 13 to 33

exception cell: C1 --> contains the title of the column

How do I do this?
 

A:Excel 2003 replace macro based on value in other cell

Read other 6 answers
RELEVANCY SCORE 118

In cell j, I have formula =IF(SUMPRODUCT(ISNUMBER(SEARCH("VLXP",K2:AB2))+0)>=1,"Yes","No") that returns yes or no if VLXP is contained in any cell K2 through AB2 and it works correctly. What I would really like to do is then put into cell j the entire matching cell content or if not found return n/a. Is there a way to accomplish this maybe with VBA?
 

A:Solved: Excel if cell contains vlxp then put matching cell data in current cell

Read other 6 answers
RELEVANCY SCORE 117.6

I need to create a macro that will run in any cell that I select. I have an excel document with information stored in rows. I need to select a row, then have it moved to the bottom of a list on a different sheet, then return me to the originator sheet. I have never written a macro before, there a several other issues that I have.
 

A:macro to run in any selected cell

Read other 16 answers
RELEVANCY SCORE 114.4

hi, i have 2-excel cells in the same sheet, both contain manually entered numbers; cell-2 changes frequently; if the existing entry in cell-1 is < than the new entry in cell-2, cell-1 should immediately reflect this new value. how do you create this formula?
 

A:Solved: excel-replace content of cell-1 if cell-2 is > cell-1

Read other 8 answers
RELEVANCY SCORE 111.6

Hello,

I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.
Code:
Sub REMOVE()


Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row


For i = 1 To p
Range("k2").Select
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i


End Sub


 

A:Solved: Clearing Cell Contents Based off other Cell

hi
try this variation;
Sub REMOVE()

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub
 

Read other 2 answers
RELEVANCY SCORE 110

If in cell A1, the user was able to write a cell number of their choice, say B5.
In the cells that they would be choosing, would be numbers. In cell A2, I want to use the contents of the cell that the user has typed into A1. Not the contents of A1, but the contents of the cell that has its cell number in A1. In this case, I would want to use the contents of B5. There will be around 15 boxes in which the user is free to write any cell number they choose. Then, in the formula box (A2) I would read the contents of these cells, and if something was there get its contents and then use the cell number that it says in a sum, in which the numbers in all of the cells which the user has chosen were added up.
What could I do?
 

A:Excel 97 Cell Contents

Read other 9 answers
RELEVANCY SCORE 108.8

Excel 2007 users having an unusual problem of cell content temporarily disappearing. Content shows in formula bar, but not in cell unless the cell is highlighted or double clicked, then contents reappear. This is completely random with no discernable pattern except that it seems to happen most often when scrolling. I've found this same question in multiple places on the web, but no solutions. Any suggestions on cause and cure?
 

Read other answers
RELEVANCY SCORE 108.8

Does any one know how to create a Formula or Function that would recognize whether the contents
of a cell are a hard coded value versus a formula ?
 

A:Excel Cell Contents Recognition

http://www.j-walk.com/ss/excel/tips/tip28.htm
 

Read other 2 answers
RELEVANCY SCORE 108

I selected a cell, and then recorded a macro when I copied the cell, moved down to the cell below it and pasted it. But when I select another cell, and then run the macro, it goes to the initial cells I did when I recorded the macro.

So is there a macro where I have selected a cell, whatever cell I decide, and then run the macro, and it copies the cell selected and paste it to the cell below the cell that was copied?
 

A:Solved: Macro to Copy and Paste From One Cell to Cell Below

Read other 16 answers
RELEVANCY SCORE 108

would like to write a macro involving cell references, which changes when the number in another cell changes

e.g if cell = 1, uses b2
if cell = 2, uses b3

please help
 

A:macro help - linking cell reference to number in another cell

Read other 8 answers
RELEVANCY SCORE 107.6

I have an excel spreadsheet. I would like to copy the contents of the table to word but I do not want it to be in a table on the word document. Even hiding gridlines does not work because I still cannot maniputate information in the cells. Is there any way to make the table inactive?

For example:
With gridlines hidden it looks like

Blah Blah Blah Blah

and I want it to look like
blah
blah
blah blah

i could cut each cell but that would take forever because I have 6 columns
and 160 rows.
 

A:EXCEL to WORD copying cell contents

Read other 12 answers
RELEVANCY SCORE 107.6

Hiya,

I'm having to make a list of users that we have but I need to put in name and login for example

Matt Hobbs | Mhobbs

Is there a forumla that I can use to read Cell A1 (Matt Hobbs) to display Mhobbs? so read the first character of the first word, then read the whole of the second word within a cell? - I've got no idea if its possible or not.

Thanks,
Matt
 

A:Excel formula to read the contents of a cell

Hi Matt, try this formula in B1
Code:

=LEFT(A1,1) & RIGHT(A1,LEN(A1) - FIND(" ",A1,1))
 

Read other 3 answers
RELEVANCY SCORE 106.4

I have an excel workbook with a "INPUT" WkSht, and a "ACTIVE" WkSht.
INPUT has a dropdown menu that looks up a list in ACTIVE, locates a desired value, and displays several cells contents in ACTIVE, - in the INPUT page.
This is working.

Question 1:
One cell in ACTIVE contains "comments" - how do I display the comments in the cell from ACTIVE, in a cell in INPUT - so I can look at all of them.

Question 2:
How do I update the contents of a cell in ACTIVE, at the same location that was selected from the dropdown.

I am a beginner - please help.

See present MACRO below...

Sub UpdateLogWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCopy2 As String
Dim myCell As Range

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D6,I18,D20,D19,D8,D10"
myCopy2 = "D5,D6,D7,D8,D9,D10"
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("Payments")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yy"
End With
.Cells(nextRow, "B").Value = Application.UserNam... Read more

A:Excel Marco Dropdown Menu Cell Contents

please do not duplicate
As your other post is in the Business Applications forum
continue there
http://forums.techguy.org/business-applications/899510-excel-marco-dropdown-menu-cell.html

closing this post
 

Read other 1 answers
RELEVANCY SCORE 106.4

What formula do I use to return a cell reference instead of contents? For example if I have a list of 100 names in Column A and I'm looking for the value "horse" I'd like excel to tell me the value is in cell A42. What's the proper way to get this result?
 

A:Excel formula to return cell reference instead of contents

if the Cell you want to test is in B1
and then the lookup range is A1 to A450

If you put this into C1 - it will return the row - I have added the A as we know its column A

="A"&MATCH($B1,$A$1:$A$450,0)

i'm sure some of gurus will post a better solution
 

Read other 1 answers
RELEVANCY SCORE 106.4

Hi all,

Cell A1 has value One
Cell B1 has value Two
Cell C1 has value Three

My requirement is to compare the contents of Cell A with Cell B and blank out contents of Cell C if contents of A <> contents of B.

I have written the formula like this:
=If(A1=B1,"",C1="") This assigns a value of True or False to cell C1 based on the values of A1 and B1.

I have also used
=If(A1=B1,"",C1=" ") The same result.

I need to blank out the contents of cell C1 completely without using any macros.
 

A:Making the contents of a cell blank in excel without macros

If you want C1 EMPTY then this is an impossible task. It needs macros.

If you merely want it to look empty then it's as simple as putting =If(A1=B1, X ,"") where X is the contents you want in C1 when the two are equal.
Or you could leave the original contents in the cell, and add conditional formatting to C1 whiting out the cell if the two are equal.
 

Read other 2 answers
RELEVANCY SCORE 106.4

Ok so here is my problem: In excel 2003 i have a nice little spreadsheet. On screen and in the print preview everything looks perfect. But when i print it out, the numbers in the cells are overlapping across the grid lines. it doesnt look pretty and its not really very professional either. Does anyone have any ideas? if you need any more info let me know.

A:Excel printing cell contents into overlapping cells

Does it happen with every font?
Are you using the TrueType fonts - indicated by TT when you select a font on the screen?
Perhaps something in the Print Setup would help?
When you do Print Preview, try the Options button, at which point some print properties of the printer might get connected correctly.

That said, I have no such problem with 2003, but in 2000 almost every font was failing with the exception of one which I don't recall. It was at work for different HP printers.

Read other 2 answers
RELEVANCY SCORE 105.2

Hello,

Can you please kindly help me to solve the below interesting issue? (I will make my best to make myself understood, which is sometimes very difficult)

I need to see DIRECTLY in Windows Explorer (I use Win Xp) what is contained in the cell A1 on Worksheet1 of an Exel file (most often, the contents of the cell is a date in special Excel date format = yyyy-mm-dd, but sometimes the contents is a text). The purpose of all this is to sort Excel files (according to the contents A1 of the particular file) directly in Windows Explorer without the need for opening the files.

I hope that this could be realizable by transferring the contents of the cell into one of the standard Excel file properties that can be seen in )Windows Explorer) Details View (eg. Description, Subject or any other property) every time on saving the Excel file.

In addition:
- If the contents of the cell is "preserve", the Excel file property should be left as it was before I opened the file.
- If the contents of the cell is "delete", the Excel file property should be (strange to read)deleted.
-----------
EXAMPLE:
On saving the Exel file, whose cell A1 in Worksheet1 contains „2012-01-05“, this date should go into the Excel file property Description.
- However, if the contents of the cell is „shall be done later“ then „shall be done later“ should go into the property. If the contents is „preserve“, the property should be left as is. If the contents is „delete“, the property... Read more

A:Solved: Transfer of cell contents into an Excel file property

You marked this as solved so does that mean you got this sorted? If so could you post your solution so others may benefit from it?

Rollin
 

Read other 3 answers
RELEVANCY SCORE 105.2

Hi all

I am trying to create a spreadsheet with a function that displays a cell's contents based on the selection of two criteria. Basically, I have a big table on one worksheet which holds data relevant to consultants at my company and their sales performance per month. It's a big old table and needs to remain in this format for various reasons. What I'd like to be able to do is have an area on a separate worksheet (within the same Excel file/spreadsheet) whereby I can select a consultant's name from a prepopulated drop down list, and also a month from a prepopulated drop down list, and have it display the sales figure which has been entered for that consultant for that month on the big data table that already exists. Is there a way to do this?
I feel sure I've seen this happen at some point quite easily but I can't remember how to do it.

If it is possible but has to use VBA then it's probably not worth your time telling me how as I have no experience with VBA unfortunately and wouldn't know where to begin! If it uses pivot tables, unfortunately I am almost as green here so a step by step method would be greatly appreciated!

thanks
 

A:Excel: How to display a cell's contents by selecting two list items

Read other 8 answers
RELEVANCY SCORE 104.4

I need to export the contents of one and the same cell to 100 different text files.

For example... I need the contents of Sheet1 Cell A1 to be exported to new text files. The names for each text file is on Sheet2 Cell A1 - A100.

Does anyone have a code example for this? I have search and found some similar but i couldnt figure out the code to modify it.

Thank you in advanced.

asm
 

A:Export Excel Cell with Macro

Hi asm,
and will the exported file be a new one or can it be an existing text file?
If the file is already presnt then what? Delete the file or append the new data to it?
Which version of EXCEL are you using?
Macro's a problem ?
 

Read other 1 answers
RELEVANCY SCORE 104.4

Hi all,
i am trying to create a summary page for a test document, each module of testing has been given its own tab in the workbook and i want to use a macro to:
1, look up the correct tab
2, find the latest test run that has been finished
3, return the test round number, the number of tests run, and the number of tests that passed.
I am using Excel 2003 edition.

I can make the macro the problem is i have to make 3 SEPERATE macros to return the 3 values. I would like to only have one that can return all three values.

the following ths the macro that i have so far:

Option Explicit
Private m_iColumn As Integer 'm_iColumn currently on
Private Const m_iColumnJump = 7 'number of m_iColumns in between test runs
-----------------------------------------------------------------------------------------------------
Function Last_Test(Round_lookup As String)
m_iColumn = 3
Do Until Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Or Worksheets(Round_lookup).Cells(3, m_iColumn) = vbNullString
'runs until finds nothing for a test run
If Worksheets(Round_lookup).Cells(3, m_iColumn) = "0" Then
'looks to see if cell contains a testrun if it doesnt then
Else
m_iColumn = m_iColumn + m_iColumnJump
'adds 7 to the m_iColumn number
End If
Loop

If m_iColumn = 3 Then
Last_Test = "Test run not started."
Else
Last_Test = Worksheets(Round_lookup).Cells(1, m_iColumn - 7)
'the last m_iColumn is the name we want
End If
End Function
---------... Read more

Read other answers
RELEVANCY SCORE 104.4

Hello!

I am new to this site and to the world of macros. I am creating a form in Excel for my company and cannot find any info for creating a macro for hyperlinked info.

Background of my form:
It's a time off request form. My dad (who knows a lot of Excel and macros (but not this kind)) has helped me get to where I am at this point.

Currently I have a dropdown for employee names. Once a name is selected, it automatically fills in their manager and department number. I have a Reset Form button to clear info out and would like to create a Submit Form button that would send the form to the manager. Is something like this possible?

I'm guessing it would need to pull from my Tables sheet.

Help! I hope I made some sense.

P.S. I will attach a sample of what I have so far.
 

A:How to have a macro add hyperlink to a cell in Excel

Read other 9 answers
RELEVANCY SCORE 104.4

I'm trying to get the cell (row & column) where a checkbox is at a excel sheet, using VB code in an excel macro.
the idea is to modify the cell properties (color, fonto, other) when the checkbox is true, and that will be only for that particular cell (where the checkbox is placed).

Any ideas?

Thx.
 

A:CheckBox cell in Excel Macro

Read other 7 answers
RELEVANCY SCORE 104.4

Hi there,
I'm attempting to make a macro in an excel workbook which will find the first unused cell in a given row and then select it, so that I can have the user input whatever information it is that they wish to input. I've got that second part of the problem figured out, but I cannot seem to figure out good code which will select the last cell in the row. I found on another webpage this code:
LastRow = Range("A6000").End(xlUp).Row
which apparently uses the End function to find the last cell in a given column and then names this cell "Last Row". This is basically the opposite of what I am trying to do, so I attempted to reverse most of what was going on in this line of the code:
LastColumn = Range("BJ1").End(xlLeft).Column
However, this code, and any variation of it which combines End and Column always ends up encountering a bug in the code and failing when used. Could anyone lend me some support here? I'm really hurting for it.
 

A:Excel Macro which Selects Last cell in a Row?

Read other 8 answers
RELEVANCY SCORE 104

Excel question:

Testing - I have a column of numbers where I do a countif (A10:A365,A1)
In this case A1 = "1" and the column contains various number from 1-10.
I want to count the number of times 1 appears in the column up to row 100

I want to substitute "A365" with a value from cell "A2" ie, A2 = 100 and make it A100
So I'd like the countif logic to be countif(A10:A100, A1)

Then if I change A2 to 150 I get the range from A10:A150.

My issue is this range is used in a number of different caluclations and I'd like to dynamically change the rows to search.

Any help/direction appreciated.
 

A:Solved: Excel - reference a value (contents of cell) for use in dynamic part of range

Try this in place of your original COUNTIF:

=COUNTIF(INDIRECT("A10:A"&A2),A1)

(the section "A10:A"&A2 builds a literal string that "responds to" A2 value ; then you just throw in INDIRECT to make it work "for real")

Rgds,
Andy

EDIT: you may prefer a different approach:

=COUNTIF(var_Range,A1)

, where the defined name var_Range refers to:

=OFFSET(Sheet1!$A$10,0,0,Sheet1!$A$2-9,1)

You can test the dynamic range "effect" by entering 12 in A2, pressing F5, typing var_Range and pressing Enter. Then repeat with 15 in A2. Etc., etc.
 

Read other 3 answers
RELEVANCY SCORE 104

sorry about that title, not sure how else to summarize it...

here's what I'm looking for - if it's even possible?

if I have a formula in a cell - IF(A1:A20>0,1,0)
how do I get the value that's greater than 0, rather than a 1 for true? in other words, I don't want a 1, I want the actual number/value (from a1-a20) that made the IF true?

thanks!
Ron
 

A:Solved: excel - how to retrieve contents of a cell found in a range in a formula

Read other 8 answers
RELEVANCY SCORE 104

I have a sheet with 2 simple columns: Date and Price. I have imported the dates (##/##/####) and the prices ($###,###) by copy/pasting from the search results given to me by a niche database program I use. When the cells paste in, they all have the format "General".

When I try to format the "date" column into dates, it _does_ change the format as far as the cell is concerned, but the content of the cell doesn't adapt to the new format. For example, I have the date as 3/05/2001 and when I change it to a date format of MMM D, YYYY the content should change to March 5, 2001 but it doesn't. It is as if all the cells are forced to stay as text regardless of what the formatting is that I'm applying.

Same problem with the price column: if I change the format to include 2 decimal points, that format does apply to the cells, but the content of each cell remains without a decimal or anything following, as if the content is just text.

I have like 1000 rows in each column, and plan to do this analysis of the database's results frequently, so I'm hoping the answer isn't just to retype the data. There's got to be a way to copy/paste or export or something. Maybe I could copy/paste into notepad first to scrub out any formatting or locking from the niche database program?
 

A:Excel 2007 Cell Values Won't Take On Characteristics of Newly Applied Cell Format

Good news: Made some progress. In thinking that maybe each value had the textual single-quote forcing it to act like text, or maybe if I find/repaced all the dollar signs and commas that had been imported, I accidentally discovered that each and every value in my imported columns has a following space!

Bad news: Seems like Excel has a bug that thinks that if I say "Find=[singleSpace]" "Replace=[null]", then I should be given an error saying "Excel cannot find any data to replace". I think I'm doing the find/replace correctly because it worked on the dollar signs and commas.

Anybody know a workaround for the bug?
 

Read other 1 answers
RELEVANCY SCORE 103.6

I'm working on a spreadsheet at the moment which displays a range of cells all containing values referenced from another spreadsheet (within the same workbook). This system works fine.

Every day, the original worksheet is updated. So, it has fields already arranged up until the end of the year. A row for every date. Now, needless to say, rows for dates in the future contain no values, and so when the spreadsheet I am working on now references those cells, it displays "$0.00" (which is correct, given I am dealing with financial figures).

Now, all of that works as expected, however, on the spreadsheet I am working on, all of those figures are displayed in a line graph. This line graph, at todays date, shows an enormous drop given that the fields for the rest of the year all show a zero balance.

What I need to do, is to get the remainder of those fields (every field that says "$0.00") to not display anything at all. So, if the value is $0.00, it would not display a value at all, and therefore not show anything on the graph.

Can someone tell me how I can achieve this? I'm sure it can be done with an "if" statement, but I'm not sure how to structure it.

Any help would be greatly appreciated.
 

A:Solved: Remove Cell Value If Cell Value Is Zero (Microsoft Office Excel 2007)

=If(a1="","",Sheet1!a1) and drag it down.

Where a1 is the first cell in spreadsheet you are working on, and sheet1!a1 is the sheet within workbook containing figure.

Not sure if the graph will recognize the "blank' cell as blank or "0"
You could try that

Pedro
 

Read other 3 answers
RELEVANCY SCORE 103.2

Using EXCEL, I have a need to copy the cell contents from upper cells in col. A down a few rows in col A. There are various changes in data in col A as you will see below. The periods in the following info are used as placeholders only. B1, A2, A3, A4, etc. are blank. I need a formula because I have 60,000 records in the spreadsheet. Thanks in advance.

Here is how the data looks now.

....A.....B
Apple.........
..........Fire
..........Ice
..........Snow
Peach
..........Sleet
..........Rain
..........Fog

Here is how I want the data to look

...A ...........B
Apple
Apple.......Fire
Apple.......Ice
Apple.......Snow
Peach
Peach.......Sleet
Peach.......Rain
Peach.......Fog
 

A:[Excel] Copy And Paste Upper Cell To Lower Cell

With the workbook open press ALT + F11 to bring up the Visual Basic Editor. Once the VB editor opens, click INSERT --> MODULE and paste the code below into the blank module. Close the VB editor and select the first cell in column A containing your data you want to copy down. Click TOOLS --> MACRO --> MACROS and select the macro from the list and run it. This macro will copy all your data except for the last value in column A because without actually seeing your workbook, I have no way knowing which line to stop at. Therefore, the code will end when it reaches the last value in column A.

Code:

Public Sub CopyData()

Do Until ActiveCell.Row = Cells(Rows.Count, "A").End(xlUp).Row

ActiveCell.Copy
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value <> ""
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Loop

Loop

End Sub


Rollin
 

Read other 2 answers
RELEVANCY SCORE 103.2

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 103.2

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers
RELEVANCY SCORE 103.2

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 103.2

In the "Checkout" tab, I am wanting to copy rows if/when a cell (column "B") in that row is greater than zero. I need this macro to go to the next row and the next row and the next row until the cell in column "B" is blank. Once the selection has been copied, I need it to pastespecial only the values and not fomulas in the "Database" tab.

I have attached an example spreadsheet.

Any and all help is greatly appreciated.
 

A:Excel Macro to copy row if cell is greater than zero

This is a duplicate of your other post
 

Read other 1 answers
RELEVANCY SCORE 103.2

I am trying to use part of a macro to change the active cell to a cell address determined from data entered by the user. For example, a user enters a date, the date is compared to a list of dates and when a match is found the cell address is returned. I want to then make the returned address the active cell. Can this be done and if so how?
 

A:MS Excel - Changing the Active Cell Macro

You would need to use VB code. I don't think you can record a macro to do that...do you already have a way to find the matching date or do you need help on that part too?
 

Read other 3 answers
RELEVANCY SCORE 103.2

Hi again

I'm doing a complicated macro that seems to run for about 5 minutes. I believe I can shorten it if only I knew how to do the following.

My macro starts at Cell A2 and scrolls down column A until a certain condition is met. The condition is that the active cell <> cell D1. The macro then stops at the active cell in column A and then copies the active cell in cell D1. More code goes here. The program then goes back to the top of column A to Cell A2 and scrolls down again until the active cell <> cell D1.

Is there a way that I can continue where I have left off in Column A instead of going back to Cell A2 and scrolling down again? Here is a concrete example. Copy A3 to D1. Code goes here. Loop until activeCell <> D1. The cursor stops at A10. A10 is now copied to D1 and the process starts all over again. The macro starts back at A2 and scrolls down. Is there a way that the macro can put the cursor back at A10, where the copy started from??

The next spot the cursor stops at is at cell A16 and then copies A16 to D1. I now want the cursor to go back to A16 instead of A2.

I hope that you understand what I'm talking about.

Mario
 

A:Do a macro in excel that return to a prevoius cell.

Read other 10 answers
RELEVANCY SCORE 103.2

Hi,

I have a large excel worksheet, divided into 4 rows (different data fields). Hard luck, in one of the rows, some cells contain two products or more instead of one (ex: "ZB3054066¦¦ZB3601207¦¦ZB3601206"). They are separated either by a blank or by a ¦¦ (double bar).
I need that each time I detect a cell in that particular row that contains multiple products, I create new rows so that each row contains only one product in the column "component parts" and that the original data in the other columns remains unchanged.
I don't know anything about excel macro, but here is the approximate code I created to do this task :
If current row cell "component parts" contains a separator (either or blank),
{
create "number of separators" new rows
copy every field except the "component parts" one
while( cell "component parts" contains a separator)
{
cut the part of the "component parts" cell before the first separator (including the separator)
paste it into the next row "component part" cell
delete the separator in the new "component parts" cell
}
}

If anyone knows how I could do this in an excel macro...
Thanks.
 

A:Excel macro : cell with multiple content

Read other 16 answers
RELEVANCY SCORE 103.2

Microsoft Excel MS Excel 2003

I would like to pull information from worksheet cells (specifically file path and name) to use in an auto open macro in order to provide a workaround on the limitation of the INDIRECT function's inability to access data in closed worksheets.

How do I create a reference in the Visual Basic macro to pul the data from the worksheet?
 

A:MS Excel Worksheet Cell info to be used in Macro

Read other 10 answers
RELEVANCY SCORE 103.2

I'm creating a macro to paste data from one worksheet into another and I need to find the last unmodified cell in a column to copy the date. The last cell will be modified by having the fill color changed. All cells with data have the fill color modified although not all cells with fill color have data.

I would use something like
While Cells("i,j") <> "",
but not all cells have data.

I need some VB method which can look at a cell and determine if it has No Fill or is filled with a color. Then I will just search for the first cell in a column which has "No Fill" and that will be the end of the data.

Hope this makes sense and thanks in advance for any help you can provide.
 

A:(ttt) Excel Macro finding last unmodified cell

Read other 7 answers
RELEVANCY SCORE 103.2

hi

my macro tells cell A2 on sheet1 to equate to cell D10 on sheet2. D10 on sheet2 is a total from the same sheet (sheet2) and sometimes can move to D8, D9 or D11. how do i incorporate the moving target cell into my macro?

tim
 

A:Excel macro, moving target cell

Read other 6 answers