Excel Macro to insert text based on contents of another cell.

[CorruptedLogic]

So this should be easy for someone who knows more about VBA than I (which is diddly squat!).

Here's what i need to do (all within the same worksheet):

There are two columns, B & C. B contains a numerical value. I need C to contain a value based on the name of the worksheet.
So...

If the worksheet name contains "TEXT1" then fill column C with "TEXT2".

Format column C as "General"

Save the workbook as a .csv to a network folder.

the rub would be that the number of rows in column C will vary each time the macro is run. can I make it so that it will only popluate column C if the corresponding row in Column B contains a value?

Clear as mud? oh good!

Thanks in advance!

CCNA, CCDP, Net+, A+
Work Smarter, Not Harder....
This was supposed to be the future.....Where is my JetPack?!

 

[AnotherHiggins]

Do you want column C to increment? (example: TEXT2 in C2, TEXT3 in C3, etc.)

How is column B being populated? If you (or anyone else) is manually putting data in column B, then lookup extend formula in Excel's help file. Basically, this feature can automatically populate column C for each new row that you enter data into column B - without any need for macros or even formulas. Not sure if that will work for your purposes, but it might.

As for saving the file as .csv, turn on your macro recorder (Tools > Macro > Record New Macro) and go through the steps of saving the excel file as you want. Observe the generated code in the VBEditor and post back here for help cleaning it up.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ck1999]

Try something like this
This assumes the following
The name of sheet is not above 9
data in col b starts in col 1

Sub test()
    
   Dim vname
   vname = ActiveSheet.Name
   
    vrows = Range("b1").End(xlDown).Row
    Range("C1", "C" & vrows).Value = left(vname,len(vname)-1 & Val(Right(vname, 1)) + 1
      Columns("C:C").NumberFormat = "General"
End Sub

ck1999

 

[CorruptedLogic]

Thanks for the suggestions. I now have something that it basically functional using this code:
Sub FillWithdrawals()

Dim FundName
Dim NotificationDate
Dim SubAgreement
Dim FundID
Dim ShareClass

FundName = ActiveSheet.Name
NotificationDate = "12/31/2007"
SubAgreement = "191331"
FundID = "247133"
ShareClass = "A"

vrows = Range("b1").End(xlDown).Row
Range("C2", "C" & vrows).Value = Left(FundID, Len(FundID) - 1 & Val(Right(FundID, 1)) + 1)
Columns("C:C").NumberFormat = "General"

vrows = Range("b1").End(xlDown).Row
Range("F2", "F" & vrows).Value = Left(NotificationDate, Len(NotificationDate) - 1 & Val(Right(NotificationDate, 1)) + 1)
Columns("F:F").NumberFormat = "mm/dd/yyyy"

vrows = Range("b1").End(xlDown).Row
Range("g2", "g" & vrows).Value = Left(SubAgreement, Len(SubAgreement) - 1 & Val(Right(SubAgreement, 1)) + 1)
Columns("g:g").NumberFormat = "General"

vrows = Range("b1").End(xlDown).Row
Range("H2", "H" & vrows).Value = Left(ShareClass, Len(ShareClass) - 1 & Val(Right(ShareClass, 1)) + 1)

End Sub

Now my challenge is to have a box pop on screen containing either a field to type "NotificationDate" or a calendar to select a date, then have a drop down list containing several options which, when selected, will change "SubAgreement" "FundID" and "ShareClass" based on what option is selected. Essentially, the drop down would have something like "Option1" "Option2" etc. Choosing "Option1" would set the fields to one value, "Option2" would set them to another value and so on.

Hope that's not too vague or cryptic!

Thanks again!

CCNA, CCDP, Net+, A+
Work Smarter, Not Harder....
This was supposed to be the future.....Where is my JetPack?!

 

[SkipVought]

FYI,

NotificationDate = "12/31/2007" is NOT a DATE.

You have defined a STRING that is formatted like a DATE, Other than the visual value, it is nearly USELESS as a value. You cannot use it in arithmetic calcualtions. You cannot SORT or COLLATE in Date sequence.

faq68-5827


Skip,
[sub]
I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent![/sub]

 

[CorruptedLogic]

Thanks for the pointer. I'll change it but it's really irrelevant as all this sheet is doing is uploading data to another applicaiton. So long as the number "looks" correct, it doesn't matter (I'm sure that tip will come in handy somewhere else though ) )

CCNA, CCDP, Net+, A+
Work Smarter, Not Harder....
This was supposed to be the future.....Where is my JetPack?!

 

[nath]

Well I am not completely sure I understand properly, bu if I do, you need to insert a userform in your VBA editor. Put a field for the date and drop down menu with your options and a start button.

Your macro first shows your form. User inserts date, select the option then click on start.

The start button is a button. on clicking you call the code you describe earlier on but passing the values of your date, and the options.

You would have in the userform a code like:

//
Private Sub CommandButton1_Click()
Call fillwithdrawals(DateValue, optionchosen)
End Sub
//

Does it give you some idea? There is quite a lot of steps to do this, let me know if you are lost or not.

Good luck,

Nate