Create Tag In VBA?
I found this really cool code that creates a tag cloud in Excel using VBA. It's far too complex for my understanding but I was hoping I could get some of the experts opinions on here about modification possibilities. I have attached the macro-enabled 2007 workbook. What I am trying to do is search through a list of keywords and determine each keyword's density within a list. The list for example could look like this:
slow windows xp
windows xp running slow
windows xp computer
[slow windows xp]
[windows xp running slow]
[windows xp computer]
[computer running slow windows xp]
"computer running slow windows xp"
"slow windows xp"
"windows xp running slow"
"windows xp computer"
You'll notice the characters " [ ] of which I would like to ignore when the cloud builds. The cloud would list the most dense keywords first, and gradually decrease to the least dense keywords. The macro code is inside the workbook but I'll list it here to:
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Auto Numbering A Tag Name
I would like to know if there is a way to Auto number a text. I have a column with text tags (lets say Column B). These cells look at a specific cell (ex. A1) and see what text is written in it then copy the text into their own cells B1, B2, B3 and so on. So if cell A1 reports AAA then Column B cells become AAA all the way down. Now what I like to do is for column B cells look at A1, copy the text and add _01 infront of their copied text. so for Column B, B1 reports AAA_01, B2 is AAA_02, B3 is AAA_03 and so on
View Replies!
View Related
Sort The Data :: By TAG And PA Claim
I have a monumental amount of data that needs to be sorted. The data is sorted by TAG number and then by the claim type EG: PA or TA and so on. There are 8 possible claim types and Each TAG may have only 1 EG: PA or it may have PA, TA, BC and so on. The thing i need to do is sort the data firstly by TAG and PA claim, then instead of having the tag number again below the PA claim type, and having the same TAG number again and next column being a TA type claim, i want to have all CLAIM TYPES IN ONE ROW......
View Replies!
View Related
Formula To Change The Tag On The Other End
I am making several thousand wire tags for a water treatment plant which need to be installed at each end of the cable runs. What I have made so far is tags for one end of the run not the other. I am looking to create a formula to change the tag on the other end so that instead of it saying 1234 to 5678 it would now say 5678 to1234
View Replies!
View Related
Set Control Visibility Based On Tag Property
I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection. I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible. Public Sub Visible1(Entry As String) Dim ctrl As Control For Each ctrl In UserForm2.Controls If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then With ctrl .Visible = True End With Else If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then With ctrl .Visible = False.............
View Replies!
View Related
Read & Write Mp3 File Tag Properties
The problem with the Write macro was that, although it did work, it used SendKeys which has to be slowed down considerably. Here is a new version of the Write macro that works as normal. I originally tried to use CDDBControl.dll version 1.2.0.51 which is widely available on the internet but found that I could only get it to change 1 file before crashing Excel. I have put the READ macro in the next message. Dim ws As Worksheet Dim FromRow As Long Dim LastRow As Long Dim FilesToChange As Integer ' number of files to change Dim FilesChanged As Integer ' number of files changed Dim MyFilePathName As String ' full path & file name Dim MyFileType As String ' mp3 wma etc. '- Dim id3 As Object Dim MyArtist As String Dim MyAlbum As String Dim MyGenre As String Dim MyTrack As String Dim MyTitle As String Sub WRITE_TO_EXPLORER() Application.Calculation = xlCalculationManual Set ws = ActiveSheet Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag") LastRow = ws.Range("A65536").End(xlUp).Row ' count worksheet rows FilesToChange = ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count If FilesToChange = 0 Then MsgBox ("No files to change."): Exit Sub FilesChanged = 0........................
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
Sumif; Find The Amount Spent Based A A "tag"
I have a spreadsheet(see attached) that I am needing to find the amount spent based a a "tag" I have created. If you bring up the spreadsheet and look at the dashboard sheet, I am wanting to find the total tag amount based off what is listed in the paychesk sheet. For tests I have used this formula and have put it in B6 on the dashboard sheet. =SUMIF('PayCheck - DEC-09-B'!$F$2:$F$1000,A20,'PayCheck - DEC-09-B'!F$2:$G$1000) But the "bank amount" field on the paycheck sheet is a negative number the value of course coming back is not right. How do I fix this?
View Replies!
View Related
Create Graph Using VBA
I want to create excel graph using VBA and found difficulties on chart name set to default. the script is like this : ActiveSheet.ChartObjects("Chart 31").Activate Can I set my own name of the chart instead of "Chart 31" (excel default) ?
View Replies!
View Related
VBA To Create New Sheet ...
I would like to be able to have a macro, linked to a button, so that when you press that button it creates a new named sheet based on data in a particular cell. I.e. There is a table with the following data: Apples (A1) Oranges (A2) Pears (A3) There are also the sheets Apples, Oranges & Pears. You enter 'Grape' in cell A4, then when you press the button it creates a new sheet named Grape. Also when a new sheet is created, the 'input cell', in this case A4, is moved down one so that when the next sheet is needed to be added, it doesn't just add the same sheet (i.e. in the example above, we don't end up with a new sheet named 'Grape' each time we click the button).
View Replies!
View Related
Create VBA InputBox
I am trying to create an inputbox using code from this site: Sub NumericDataType() Dim lNum As Long On Error Resume Next Application.DisplayAlerts = False lNum = Application.InputBox _ (Prompt:="Please enter you age.", _ Title:="HOW OLD ARE YOU", Type:=1) On Error Goto 0 Application.DisplayAlerts = True If lNum = 0 Then Exit Sub Else MsgBox "You are " & lNum & " years old." End If End Sub I get an error on the first line that says, "Compile error: argument not optional".
View Replies!
View Related
Create Comparison Code In VBA
I have to do monthly comparisons of two excel worksheets, one being a vessel timesheet and the other being an excel download from manpower software that we use. The comparison that is being made are days of the month, whilst the unique identifier between the two sheets (To make it easier to understand I will call them sheet1 and sheet2) is an employee ID. Both sheets, alongside the name and employee ID, list all the days of the month that employees have worked. If they have worked, there is a "1" under that day, if they have not the cell is blank. What I currently do manually is: 1: Search for the employee ID (and/or name) in sheet1 and compare the days with the employee ID in sheet2. 2: Highlight the differences 3: Move onto the next employee ID All differences are highlighted in sheet2. Differences highlighted are in the days only, where if any days are missing/extra in either sheet, then the particular cell(s) are highlighted in yellow. If an employee is missing from sheet1 but is in sheet2, then all the days are highlighted (in sheet2). If an employee is missing from sheet2 but is in sheet1, then that particular employee is copied to the bottom of sheet2 (after last row). As this is horribly complicated to explain, I can attach an example if this is sounding rather inexplicable? Is it possible to attach a xls to a thread?
View Replies!
View Related
Create A New Workbook By Vba Of Excel
I want to the following steps to be done by vba of excel. 1. copy ranges of cells in the active worksheet (sheet3) 2. add a new workbook and paste the copied ranges to sheet1 3. save the new workbook with the new file name (e.g. date) 4. close the new workbook and clear the contents the copied ranges of cells in old workbook. following code and make it more simpler? Worksheets("Sheet3").Activate If Range("l1").Value <> "" Then Range("l1").Value = CDate(Date) End If asdate = Range("l1").Value 'date entered by user resp4 = MsgBox(" Case particulars of " & asdate & " will be backed up and deleted from this sheet! Proceed? (Y/N)", vbYesNo, "Deletion of Data") If resp4 = 7 Then Exit Sub
View Replies!
View Related
Vba To Create A New File From Spreadsheet
I have the following spreadsheet: A-------------B File No.-----PRODUCT TYPE 123456-----NDT 123456-----NDT 123456-----NDT 123456--------- 123456--------- 789110--------- 789110--------- 789110--------- What I need is if there is an NDT in Column B and 123456 in Column A then I need to eliminate all rows which contain 123456. End result A 789110 789110 789110
View Replies!
View Related
Create Outlook Appointment From VBA
I am probably overengineering my macro, but I would like to setup an outlook appointment in two peoples calendars from my userform. The macro has access to the email addresses of the selected persons, I just want to send date/time and subject.
View Replies!
View Related
VBA To Create A New Folder From A Textbox Value
I don't know if anyone has tried this but it possible using vba to create a new folder in a directory from the text of a textbox? I have made a userform that I use to collect a persons first name & surname. I then created a folder in a directory manually (e.g Z:spnzphotos) I then scan their photo and place it into the folder. Is it at all possible to have a piece of code that once I press a command button is creates folder ready for me to add the photo to?
View Replies!
View Related
Vba To Create Separate Reports From Master
I have the following spreadsheet Columns on master sorted by Column J: A--------------E-------------J SUPPLIER----CARRIER------LOC CODE BELGIUM-----HAM-----------BP HYDRO-------MSC-----------BP DAINIPPON---NNR-----------BP BELGIUM------YM-----------BR BELGIUM------YM-----------BR HYDRO-------MSC----------CA BELGIUM-----HAM-----------CA HYDRO-------MSC-----------CA HYDRO-------MSC-----------DE DAINIPPON---NNR-----------DE BELGIUM------YM-----------DE BELGIUM------YM-----------DE What I need too do based on the Codes in Column J is create a separate workbook for each different code.
View Replies!
View Related
VBA Macro To Create New Worksheet, Charts, Etc.
I need a macro to create the following worksheets and charts from an Excel data set: Three (3) worksheets (already created manually in attached Excel file): 1. Chart Data. (a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr). (b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset. 2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data). 3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data) At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.
View Replies!
View Related
Can VBA Create A Named Sheet In One Step?
My vb code currently creates a new sheet via "Sheets.add". This new sheet has the default name of "sheet1" which I rename as "tempSheet". I then populate tempSheet with data, save as a CVS file and then I remove tempSheet. If I stay in my workbook and try to execute the code again, the default name of the sheet it now creates is "sheet2" and not "sheet1" - this is a problem becuase my code is trying to rename "sheet1" "tempSheet" and of course it cant.
View Replies!
View Related
VBA - Create Outlook Message With From-field
I have two question for Excel in combination with microsoft outlook: 1. I was wondering if it is possible to create an email from Excel which automatically adds the From-field with a pre-defined emailadres. I am using CreateObject("Outlook.Application"), but I can not add the From-field. 2. I also would like to know if it is possible to do automatically fill the To-field with the emailadress from the user who send me the original file. Maybe this needs some more explanation (sorry my english isnt too good): * A user can download an excel-form from the internet * The form is sent to my emailadress * I give a number to the form * The user will get a message that his question is attend to with the number of the question So I use a macro-button to reply when I have filled in the question-number.
View Replies!
View Related
VBA To Create A Hyperlink Drilldown For Each Cell
I need help in making a VBA to create a looping hyperlink drilldown for each cell in the inventory_general wksht. The purpose of this sheet is to allow a user to click on a hyperlink which will then reference a macro to autofilter the data in the bin_lot worksheet to display all rows for that particular item number. Apparently, you can't select all the cells and insert a hyperlink because all the hyperlinks will reference the first cell value for autofiltering. This is what I tried but it's not working obviously. Sub HyperlinkDrilldown() ' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "Bin_Lot!A1", TextToDisplay:=target.value Range("A2:A21").Select End Sub
View Replies!
View Related
VBA - Create Folder With Date In Name, Populate With Sheets
I have a workbook that will have many modules (5) that will perform all sort of checks etc. The output for each module is a worksheet that i need to export to a new workbook named with the name of the original sheet and current date appended e.g. "Dbase 27 06 2008.xls". Every time any of the 5 modules is run a check is performed whether a folder with a specific name and date exists e.g. "Audit Tool 27 06 2008" and if it does not exist yet to create it. The new workbook should be saved to this folder with above mentioned name e.g. "Dbase 27 06 2008". I found some info on this topic but I just cannot assemble all pieces of code together (I am new to this).
View Replies!
View Related
VBA Macro Code To Create Multiple Charts
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want: Sub Macro5() ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80" ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80" ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveWindow.SmallScroll Down:=-3 End Sub How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).
View Replies!
View Related
Create A Named Range In VBA For Column A, Which Is A Sum Of B And C
I would like to create a named range in VBA for column A, which is a sum of B and C. The problem is that A can have 0 as a value. What I would really like to do is define the named range in column A as A2 to the last column with a value in column B(B10) which would make the named range in column a A2:A10. ABC 61555560 00 1250120050 725725 00 850850 1919 7216720016 995995....
View Replies!
View Related
VBA To Create Charts/Graphs From Non Continuous Ranges
I have an excel sheet populated with loads of values. There is a space between each set of data. I need to draw a graph for each section and i cant work out how to do it. I have attached the sheet in question. The gap between each column has "space" written on row, which is needed to prevent it being deleted. The drawing of the graphs needs to be automated as there is going to be 100's of sheets containg lots of data. The column size of each section can change so it needs to be dynamic. I have attached an example sheet
View Replies!
View Related
Create A Single Range From Multiple Ranges For VBA Input
I have a VBA function with the header: cubspline(Xval As Double, XRange As range, YRange As Range) As Double The problem is that XRange (and also YRange) is in different areas of the spreadsheet. I want to combine these areas into one range which I can pass on to the cubspline function. What is the easiest way of doing it? I'm looking for something like a "union" function for ranges in Excel.
View Replies!
View Related
VBA To Create Word File And Add A Custom Property To It
I have an Excel sheet with a long list of data. A short example is shown below: Section | Title | Item 1 | INTRODUCTION | a 1.1 | title2 | b 1.2 | title3 | c 1.2.1 | title4 | d 1.2.2 | title5 | e I made a VBA macro in Excel that runs through this list and creates a new Word file for each item. The filename of the document is based on the data in the Excel file (section and title). Now I would like to add a custom property to each of the newly created Word files, i.e. the value in the 'item' column. Does anyone of you know how I should do this? Or should it be better if I write a macro in Word that runs through the Excel data to create the word files? Here is the code I use to generate the word files:
View Replies!
View Related
Create SQL Query In VBA And Display Result As Table In Worksheet
I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet. Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this: No. Timestamp Tags Value 1 1-Jan-2010 Tag A 18 2 1-Jan-2010 Tag C 20 3 2-Jan-2010 Tag A 20 4 3-Jan-2010 Tag B 17 5 3-Jan-2010 Tag C 19 6 4-Jan-2010 Tag B 18 7 4-Jan-2010 Tag A 20 8 5-Jan-2010 Tag A 22 9 5-Jan-2010 Tag B 18 10 5-JAn-2010 Tag C 20
View Replies!
View Related
VBA Macro To Create & Format Text Box On Worksheet
I have code that creates a textbox on a worksheet, copies the contents of a cell from another worksheet to that textbox. I want to be able to select that text and format its appearence ( center the text, bold and font style and size). This is what I have so far but I do not know how to select the text that was imported. Sub textmove() Dim bname As String Sheets("cover").Shapes.AddTextbox(msoTextOrientationHorizontal, 96.75, 512.25, _ 230.25, 120#).Name = "client" bname = Sheets("data").Range("a3").Value Sheets("cover").Shapes("client").TextFrame.Characters.Text = bname With Selection.Characters(Start:=1, Length:=17).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End Sub
View Replies!
View Related
VBA Macro To Create Multiple Data Validation Lists From Variables & Named Ranges
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is Public Sub assignDVList(WSD As Worksheet, sListName As String) Dim DVListName As String DVListName = "DV" & sListName Application.Goto Reference:=sListName With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & DVListName It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
View Replies!
View Related
Create A Chart To Create Totals Based Upon Different Keys
Trying to create an excel chart to create totals based upon different keys. I need to be able to calculate how many customers there are by Manager and then By Rep. Then to figure out how many were New, Current, Total # of RSVP and attended for that Rep. Below is how I have started but I am having some problems getting certain parts. I know when I get one the rest will fall into place. I can calculate how many total customers by manager and by rep just by doing a Countif command but how do I determine the # of New, Current etc. Is there a If Than command? Managers Totals are simply his reps totals. Example.. A B C D E F G 1 Manager Rep Customer New Biz Current # RSVP # Actual Attend Need totals to look something like this.... A B C D E F G 1 # of Cust # New # Current #RSVP # Actual Attend 2 Manager 3 Rep 1 4 Rep 2
View Replies!
View Related
Vba Error Run-time 429 Activex Component Can't Create Object - Excel & Outlook 2007
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error: Run-time error '429': ActiveX component can't create object and is stopping on the line Set OutApp = GetObject(, "Outlook.Application") FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error. Dim OutApp As Object Dim OutMail As Object Dim SigString As String Dim Signature As String 'Set OutApp = CreateObject("Outlook.Application") Set OutApp = GetObject(, "Outlook.Application") If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0)
View Replies!
View Related
Create Macro Code With VBA Code
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!) The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code... The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work). The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments. 'this code calls the method which creates the buttons in specific cells with specific names Private Sub CommandButton3_Click() 'the first call always works! AddCmdbuttonWithCode "Edit_111_111_114", 23 'the second one crashes excel AddCmdbuttonWithCode "Edit_111_111_115", 27 End Sub
View Replies!
View Related
|
TRACKER
