Solved

Convert cell value to cell comment

Posted on 2015-01-09
3
147 Views
Last Modified: 2015-01-09
Hello,

i need help with VBA,

on selection of a range, i want to run a macro that moves each cell value to cell comment and erases the cells and puts 1 in each of those cells.

thanks.
0
Comment
Question by:Flora
3 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 250 total points
Comment Utility
Hi,

pls try

Sub macro1()

For Each c In Selection
    Set cmt = c.Comment
    If cmt Is Nothing Then
        c.AddComment
    End If
    c.Comment.Text Text:=CStr(c.Value)
    c.Value = 1
Next
End Sub

Open in new window

Regards

EDIT test if already a comment
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
Comment Utility
Sub Convert()
Dim strString As String
For Each myCell In Selection
    If myCell.Value <> "" Then
    myCell.AddComment
    myCell.Comment.Visible = False
    strString = myCell.Value
    myCell.Comment.Text Text:=strString
    myCell.Value = "1"
    End If
Next
End Sub

Open in new window

0
 
LVL 5

Author Closing Comment

by:Flora
Comment Utility
both codes were excelent

thank you both.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now