[RESOLVED] copy user entered data to new sheet

[DebbieInFlorida]

Hi,

I tried but didn't find this answer. I need a macro in a cell of Sheet1 to be copied over to Sheet2 as the user enters information. But it doesn't stop there, it also has to convert the information. ie, if the user enters 20, then this must be converted to a string of "medium".

I tried this, but it didn't do anything
wkbSheet2.Range("F" & rowCt).Value = wkbSheet.Range("F" & rowCt).Value


THanks

[MarMan]

How do you set rowCt?

You can use a Select Case statement to change 20 to "medium"

[DebbieInFlorida]

sorry, I should've replaced that, it is a row counter, just pretend it is a valid row number

wkbSheet2.Range("F1").Value = wkbSheet.Range("F1").Value

I understand how to use Select Case statements, but was wondering how to put a formula in a cell.
Besides, what I have here doesn't set the cell to a macro for when the user enters a value??

[MarMan]

To put a formula in a cell prefix it with =

I did a search on excel assign macro to a cell and that was the first choice.

[DebbieInFlorida]

Groovy, this is getting somewhere, but I should've elaborated. I am creating this excel file from VB.net, so I need to have this embedded in the sheet while creating it. hhmmmmmm??

Now what?

[MarMan]

You can record a macro in excel. That will give you an idea on how to do it in .NET.

[DebbieInFlorida]

I know how to put a macro in Excel while in Excel. AND I know how to create the Excel file from VB.Net, What I need to know is how to Programmatically create the Excel file while adding/embedding macros/WithEvents in the worksheet.
This will then allow the user with the finished Excel file to enter data, which the WithEvent macro will massage and put into another cell.

[westconn1]

when you say macro in cell, do you mean formula?

if so try like

range("A1").formula = sht2.range("b6").formula

if not, please elaborate on what you wish to achieve

see the tutorial for automating office applications in the faq thread at the top of this forum

[DebbieInFlorida]

Thanks for your reply, and you are right it is a formula I want, got my apples mixed with my oranges.

I could not find the tutorial you mentioned.
I tried your example and it did nothing for me, this is a version I tried with no luck either...
wkbSheet2.Range("G7" ).Formula = "= " & wkbSheet.Range("G7").Formula

also

wkbSheet2.Range("G7" ).Formula = "= " & wkbSheet.Range("G7").value

neither did anything.

The final result I really need is when the user enters a number, then a calculation is done on it and a text value is put in the other sheet. ie user enters 25, then "Low" is put in the next sheet, value of 75,then "High".

Any help is appreciated, I have been struggling for 2 days.

[DebbieInFlorida]

me again, still plugging at this. I can do this by hand in Excel!!!

my latest effort.
Dim formula As String
formula = " =focus!F" & rowCt
wkbSheet2.Range("F" & rowCt).Formula = formula

where "focus" is the name of the sheet I want to copy info FROM to wkbsheet2 as it is entered by user.
all this does is put the text value of the formula into each cell.

???

[Siddharth Rout]

DebbieInFlorida, you need to use the worksheet change event to achieve what you want.

Do you have a sample file?

Sid

[DebbieInFlorida]

Thanks SID, but you are wrong!! I just figured it out myself, I am so happy I am going out for a beer after this entry.

this is the magic answer..

dim formula as string
formula = "=" & sSheetName & "!G" & rowCt ' ' this works

wkbSheet2.Range("G" & rowCt).Formula = formula

'== sheetname is the name that I set the first sheet to.

YippEE!!

[Siddharth Rout]

Thanks SID, but you are wrong!!

Are you sure?

Sid