Visual Basic: Select The Column (cell) Left To The One That's Currently Selected.
I'm trying to get a code that will do the following: I've got a list of stocks followed by extra information in de columns to the right of it. If, based on that extra information you'd like to know more about the stock, I'd like the user to just select the cell that holds the name, and click a button.
What this button should do is the problem for me right now: it needs to select the cell left to the one selected holding the name of the stock.
The cell to the left holds the code that is used to lookup information in Bloomberg. This cell is invisible to the user since I hide it to make the tool look good. How do I write a piece of code to select for instance: B10-1=A10. The rest is just a recording job.
Also, I know you can add text to the text in a cell, however in this case I want to cut some text. Is the any way to get: "B10-1"-"equity sedol1", leaving me with the text in A10 minus equity sedol1?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Using VBA Code In Visual Basic: Develop A Stand Alone Program In Visual Basic
I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts. But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention. Then once it updates, I import the updated *.pri file back in our Portfolio program. I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). how to use external files and Excel VBA code in Visual Basic? Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet. Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String) 'Below are Excel VBA codes 'Uses the test.csv to look up tickers in test.pri and update the price in it Dim PriceFile As Workbook, BigChartFile As Workbook Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet Dim MaxRows As Long Dim PriceFileRow As Long Dim BigChartRow As Long Dim BigChartFound As Boolean Call CheckBookOpen(BigChartPath & BigChartName) Call CheckBookOpen(AxysPricePath & AxysPriceName) Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv...............
View Replies!
View Related
Visual Basic - Not Allowing Cell Entry
I have a cell that I do no want anyone to enter any information into unless a previous cell has "U" inside of it. Okay so so I have a column of cells named "Fringe Benefit Type" you can choose different letters to corropand to different types. Now I have set the letter "U" to be the one I want and that works. Now the next cell is called "Hire Season". Now (stay with me here) IF "Fringe Benefit Type" is equal to "U", I don't want the user to be able to enter anything into "Hire Season". I am using the visual basic thing, which already has some code, but I am not understanding it too well.
View Replies!
View Related
Visual Basic And Images
Is there a code you can type in to VBA to allow a picture in a spreadsheet to change when a parameter is specified. For example i have a picture of a timber beam i drew in excel. and i wondered if i could set it up so that when the user inputs the length of beam they are using, the diagram alters to show the length specified.
View Replies!
View Related
Visual Basic Opens Automatically
I have a VB problem, and it's driving me nuts. Whenever I run a macro on excel the Microsoft VB editor opens and shows me what the macro looks like. It also runs the macro. Now, I would very much like it to keep running macros, but I really don't need to see my code every time. It gets really annoying, and I need to run about 40 or 50 different macros on one sheet. How can I make it go away? PS! It's Excel 2007 for students, Norwegian version.
View Replies!
View Related
Lost Visual Basic Project
Have a project going and have had a disturbing trend develop. I'm working in Excel 2003. Upon opening the file and enabling macros, I started receiving File/Path errors, at which point it would shut down the application. When opened, I have a splash that would come up, but it wouldn't go to the next userform. At this point I would get the error message and the file would be closed. If I then reopened the file, disabled macros, saved under a different file name, and then reopened with macros enabled, everything worked fine, until I shut it down and then tried to reopen, at which point the same error would occur. Lately I have simply been getting the Microsoft Excel dialogue box telling me an unexpected error occurred and it had to be shut down. I'm at a loss for what is causing this. The last time this happened, when Excel recovered the file, it told me a repair had been done, and the repair was to basically delete all of the VBA code from the file.
View Replies!
View Related
Open Workbook With Visual Basic (VB)
I am trying to open a closed workbook, auto update and save. Here is the code i have so far: Dim xlBook As Excel.Workbook Try xlBook.Add() xlBook.Open(FileName:="C:Old Tanglewood Numbers.xls", UpdateLinks:=1) Now, the xlBook.Add() line is something I just added. I am not sure if it works but i dont think it does. Everytime i try to use Workbooks.Open it tells me that i need to decalre Workbooks, which i thought i did with xlBook. Anyway, I cant get it to work, something is throwing it out of whack.
View Replies!
View Related
Entering Codes In Visual Basic
Do you enter codes on your own or do you just click on the commands in the top right scrollbar in design window at most of the time? I'm teaching myself Excel VB but I'd like to know if most of you just click on the command for codes as you go along or you can enter the codes yourself without the help of the command buttons
View Replies!
View Related
Visual Basic To Access Web
My company "makes widgets". Each "type of widget" is associated in our internal database with a record locator (five alpha characters). Our company website can also use that record locator to pull up a webpage specifically dedicated to that widget. In addition to the webpage itself, the website also creates an expanded URL which contains additional information about that widget. I'd like my macro to take a list of record locators, open up the company website related to each specific widget, pull back the expanded URL, and do some text management with the URL to populate other parts of the spreadsheet. For example, if my record locator is "ABCDE", my macro would go to the website, "http://www.widgets.com/ABCDE". The site then redirects to an expanded URL: "http://www.widgets.com/whole-bunch-of-data-about-the-ABCDE-widget". I'd hope to write a macro which pulls back the expanded URL and does some magic with the "whole bunch of data" part. I've been told that the MSHTML library would be useful for a project of this type. I've been able to connect my spreadsheet to that library, but then I'm not sure what to do with it.
View Replies!
View Related
If Statement - Visual Basic For An Input Form
I am creating a user input form where the user can enter data about what they have eaten on a certain day. I am using radio buttons, so when the radio button has been clicked, it automatically puts something into cell A1. What has been put into cell A1 will then be used to say what is in the combo box named Cbo_FoodItem. I have used the following With Me.Cbo_FoodItem If Worksheet("na").Range("A1") = "carbs" Then Cbo_FoodItem.List = Worksheet("foodtable").Range("B2:B215").Value End If End With However, when i try to test it, i get an error which then highlights the "Worksheet" after IF and says "Sub or Function not defined". Obviously there is something wrong with the formulae, however i cannot work it out. Please help. I've only just started using visual basic, but i am starting to get the gist of it.
View Replies!
View Related
Visual Basic Auto Filter Macro
I have a excel file that was created by someone else 7 years ago - I still want the formulas that were created but I have updated the information on the spreadsheet and now the macro will not work Sub DataSort01() ' ' DataSort01 Macro ' Macro recorded 11/18/00 by Terry Schiesser ' ' Keyboard Shortcut: Ctrl+t ' Selection.AutoFilter Selection.AutoFilter Field:=2, Criteria1:=">0", Operator:=xlAnd End Sub when I do the ctrl+t it wants to debug I select the debug option and then it goes to the screen above but the "Selection.Autofilter" is highlighted.
View Replies!
View Related
Microsoft Visual Basic Error 400
My code here... Sub ClearSheet() Worksheets("Summary").Activate If Range("CustInfo") = False Then Range("ICompany, IPhone, IFax, IContact, ICell, IEmail, IAddress, IPOBox, ICity, IState, IZip").Select Selection.ClearContents Else Range("IJobDescription").Select Selection.ClearContents End If For I = 1 To 5 Range("Qty" & I).Select Selection.ClearContents Next End Sub keeps giving me an error 400. I have reinstalled xl just to see if it was related toan error I recieved earlier relating to VBE6.dll. This still works in another workbook but it doesn't work in this one. All I did was change the Range names and cleaned it up a bit.
View Replies!
View Related
Visual Basic Compiler - Odd Performance
I am encountering a situation wherein each line of VBA code is essentially compiled as I type on that line, instead of when I move to a different line. For example, if the code I entered is incorrect, the compiler waits about half a second and then turns it red. Also, if I enter a space, it deletes it immediately.
View Replies!
View Related
Visual Basic Editor Windows Moved
I have a problem with my VBA Editor. I have the Projects Window at the top left of the screen, the Properties underneath it and, normally, the Userform/Code window fillint the rest. However, The userform/code window is not behaving as before. When you try to get one or the other they open in another window whereas I'm pretty sure that they just opened in the same window.
View Replies!
View Related
Word Wrap In Visual Basic Editor
I am having trouble entering all the text in a single line in the Microsoft VB editor (the application that opens when "view code" is selected in Excel. The code I am trying to enter is: vUser3 = Array("Login", "CLREL Client Relations Sum.", "CLRMGT Client Relations Mgmt.", "Client Relations Admin", "Sales Support", "Admin Officer", "Admin Support", "Trade Show Mgmt","Client Rel Prof Serv", "Client Rel Imp.", "Sec. Mkt & Inv. Clients", "SALES - Sales Summary", "Sales Admin", "Cross Selling", "Loan Fulfil. Service Sales", "Channel Management", "Corp Div. Mgmt.", "Strategic Sales", "SALN - Sales Northern Region", "SS-Gleason", "SS-Collins", "SS-Hodum", "SS-Pluchino", "SS-Robbins", "SS-Sandbach", "SS-Johnson", "SS-Conner", "SALW - Sales Western Region", "SS- I realize that this is very long, but the VB editor does not see it all as the same line, and therefore I get a Compile error: Expected: list separator or ). Is there a way to have this all on the same line?
View Replies!
View Related
Visual Basic Editor Commandbar Control
'The following code creates a new commandbar name Visual Basic Editor, which on event delete the VBE module1. How can I tie the built in visual basic editor commandbar to this. Sub bar() Set mybar = CommandBars("Tools").Controls("Macro") Set mycontrol = mybar.Controls _ .Add(Type:=msoControlButton) With mycontrol .Caption = "Visual Basic Editor" .FaceId = 1 .OnAction = "MySub" End With mycontrol.Visible = True End Sub Function mysub() With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule .DeleteLines 1, .CountOfLines End With End Function
View Replies!
View Related
Visual Basic Form Asc Library
I have four columns in my worksheet. In column C, I need to find the no. of times word "Alarm" is occuring. Further, with each "Alarm" entry in col C, there is a alarm type given in column D. I need to find the no. of alarms for each type and then display a bar chart for the alarm type vs. frequency of its occurence.
View Replies!
View Related
Visual Basic Editor - Creating Macro Prompts
I have created macros before, but it was a step by step, easy to follow, guide from out textbook. Now I have to create one on my own from these directions. So my first task was to create a macro that went to a specific worksheet, and made A1 the active cell. I created that macro. Now it comes to where I have to open the macro in Visual Basic Editor and I am completely lost. I know absolutely nothing about this. This is what it looks like now: Sub Show_Report() ' ' Show_Report Macro ' Macro recorded 4/25/2007 by ******* ' ' Keyboard Shortcut: Ctrl+w ' Sheets("Jeff").Select Range("A1").Select End Sub So now these are the next directions. "Edit the Show_Report macro in the VBE so that the macro prompts the user for the name of the report to view (indicating that the user should enter "Jeff", "Sally", "Jane", "Jim", and "Harry") and then displays that worksheet. (HINT: You should run the edited macro to make sure that it runs correctly.) Include an If-Then-Else control structure to detect an error if the user enters an incorrect report name. If an error occurs, the macro should display a message box informing the user of the error. So what Im thinking is I need a code so what when I hit my macro hotkey, it opens up a pop-up box and then I get to type in a worksheet name and it takes me to that worksheet.
View Replies!
View Related
Macro Has Errors If Visual Basic Editor Is Not Open
I've been helping another user create a workbook that dynamically adds, renames and deletes worksheets from a "Main Sheet". I have got the whole thing figured out and running to satisfaction, except... The macro runs fine if the Visual Basic Editor is open. If the editor is closed while the macro is run, I get "Runtime Error '9': Subscript out of range" Any ideas what could be casing this? The errors occur when attempting to add sheets.
View Replies!
View Related
Visual Basic Editor Font Settings For A Specific Workbook Only
I only want to change some Visual Basic Editor (VBE) font settings on the active workbook, but whenever I do so within the Editor tab of the Options dialog it changes the VBE font settings for all workbooks. Is it possible to change just the active workbook VBE font settings or do these settings reside in some *.ini file that once changed, globally changes the setting(s).
View Replies!
View Related
VBA To Identify Top Left Cell Of A Selected Range
if a user has selected a range, how do you, in VBA, identify the: 1. Top left cell 2. Bottom left cell 3. Top right cell 4. Bottom right cell For example if user has selected the range B5:M30, then we would want to identify in the macro: 1. Top left cell = B5 2. Bottom left cell = B30 3. Top right cell = M5 4. Bottom right cell = M30
View Replies!
View Related
Quick "msgbox" Question (Visual Basic) / Allow User To Respond With Text ? []
I have searched the 'net and read MSDN msgbox but I cant find what I am looking for. Maybe msgbox is not the correct function to be focusing on?? I want a message box to ask a question, and the answer is dumped into a variable, much like you can do with yes/no/cancel, but (much like a pop-up textbox) I want to be able to respond with text in a textbox. [This of course is part of my Excel spreadsheet] (I think with C++ it was something like "msgbox = ?$-" or something like that, but I don't know what to look for with VB.)
View Replies!
View Related
Macro To Only Show Rows That Contain Specific Text/Chinese Text Issue In Visual Basic ( 2007)
I want to create 3 command buttons (active X) on a worksheet to toggle between showing rows which only contain the below text in column L (range L9:L30) and showing all rows containg the options (However, I also have some blank rows in this range and i always want them to remain hidden.) My text options are: High ‚ Medium ’†“™ Low ’á The text arrives in the cells via a VLOOKUP Is the chinese text a problem? i can't type it into VB. I've been using the following macro to hide and unhide rows with a command button in the same sheet: Private Sub CommandButton1_Click() Toggle_Hide_Unhide End Sub Sub Toggle_Hide_Unhide() Dim rngCell As Range Dim TakeAction As Boolean If ActiveSheet.CommandButton1.Caption = "Hide" Then TakeAction = True ActiveSheet.CommandButton1.Caption = "UnHide" Else TakeAction = False ActiveSheet.CommandButton1.Caption = "Hide" End If For Each rngCell In ActiveSheet.Range("I9:I30") With rngCell If .Value = 2 Then .EntireRow.Hidden = TakeAction End With Next rngCell End Sub
View Replies!
View Related
Programmatic Access To Trust Access To Visual Basic Editor
I am Generating Excel file with Macro using my asp.net (c#) application. I am able to generate Excel file in development environment, but in Production it gives following error: "Programmatic access to Visual Basic Project is not trusted Line: Microsoft Office Excel" I did googling a bit and found that I have to open Excel file physically make few security related changes in macro as below. 1. Open the Office application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box. 2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access. 3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template. Can i do above changes at runtime (using some code)?
View Replies!
View Related
Select Row Where Only 1 Cell Is Selected
I need to delete part of the row where one cell is selected. If more than 1 cell is selected, I want a msgbox. Example: I have any cell selected on row 26. I want to select all cells in this row including column C thru column AD. Column A & B are not to be deleted. After the selection is made, I want the row deleted and the below cells moved up.
View Replies!
View Related
Macro: Select Cell A Of Selected Row
Currently working on a project for university and still learning how to use macro. Got a problem which im sure is an easy solution for all you experts out there. As shown in the picture; http://img300.imageshack.us/img300/1205/macrohelp.jpg. When the Sap number is entered (from A19 and onwards), it copies the value to cell B10. The general code is used below.
View Replies!
View Related
VBA- Can't Paste Into A Newly Selected 'activecell'
I am new to VBA and was just wondering if there is logic behind this or am I doing something wrong.... ie, 1 Cells(1,1).Select 2 Activecell.Copy 3 Cells(2,1).Select 4 Activecell.Paste The above doesn't work but if I use the same above 3 lines with 4 Activesheet.Paste it does work What is logic here as to why it can't paste into a newly selected 'activecell'?
View Replies!
View Related
Using 'left' Function To Take Out Number Upto A Certain Character
Pretty sure this has been asked but have searched the forum to no avail, but I need to extract the numbers from a value which has a letter on the end. eg. 1000x I need to get out the 1000 or 2p I need the 2 I have sooooo many values ranging in numbers of digits, so just basic left(A1,2) won't work, I'm sure I've seen a search or find function but don't know how to use them!
View Replies!
View Related
Store The Selected Cell's Address Into A Variable
I have a table in which I have a "patient" column and a "page" column. The macro searches for a patient's number, then looks if this patient has the page "900.--". A patient may have more than one page, which will result in many rows with the same patient number. So far, my macro uses the search function to find a patient number, then compares the cell next to the active cell to see if it contains the page "900.--". If not, then my macro searches for the next patient and so on until the page is found and noted into another workbook or none is found. In order to stop the loop, I am trying to store the address of the first cell found into a variable "rFirstCell" so that it can later on be compared to another variable, "rSecondCell", which represent the active cell. When both are the same, it means all the available search results have been tested and the loop should stop. Sub testing() Dim rRng As Range, rFirstCell As Range, rSecondCell As Range Set rRng = Worksheets("Overview").[a1] Dim sDeath As String sDeath = "death" ActiveSheet.AutoFilterMode = False If LCase(rRng(2, 15).Value) = "x" Then If LCase(rRng(2, 9).Value) = sDeath Then Workbooks("DM Endpoint pages_test.xls").Activate Range("A1").Select...........................
View Replies!
View Related
Range Copy: Differ Depending On The Active Cell That's Selected
The rows will differ depending on the Active Cell that's selected and I don't know how to specify this. The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got 'FindRemove = lstRemove.Value 'If FindRemove = "" Then End ' Goes to the start of the Data column 'Sheets("Staff").Select 'Range("B4").Select ' Tests current cell against FindRemove 'Do 'If ActiveCell.Value = FindRemove Then 'Call CopyPerson 'Exit Sub 'Else: ActiveCell.Offset(1, 0).Select 'End If 'Loop Until ActiveCell.Value = "" 'End Sub
View Replies!
View Related
If Blank Cells, Paint Yellow. If Cell To Left Is Less Than A Number, Don't Paint
I have a spreadsheet which contains 39 columns with a header row. The number of rows varies per spreadsheet. I'm running into a problem with highlighting(painting yellow) cells. Really the problem is selecting the cells to be highlighted. The macro is skipping some of my code completely and in other cases leaving some cells unhighlighted. 1. All blank cells in row AG should be painted yellow. 2. If column AF contains a cell with number 900 or greater, then the cell in AG (same row) should NOT be painted yellow. 3. All blank cells in rows AC and G should be painted yellow. Some spreadsheets may not contain blank cells. Problem with the VB is not performing the code after the first command. It will highlight MOST of the cells in AG but for some reason it is leaving a few blank ones unhighlighted. The blank ones are usually at the bottom of the rows. I have no idea why it would miss these. I believe the "ucase" tag is incorrect since its looking for a number but I am not sure what to put in its place and if I use something else should I dim it as ""? I've attached an excel spreadsheet which contains a sample to this problem. I've also attached what it should look like if my code worked.
View Replies!
View Related
Using 'IF' With Conditional Format (change Cell Color Based On The 'P' Or 'F')
My cell has a 'P' or an 'F' plus some additional text. I'm trying to change cell color based on the 'P' or 'F' but I can't get it to recognize the letters. Samples of what I've tried: =IF(LEFT(L23,1)=P) =IF(MID(L23,1,1)=P) etc. I can 'FIND' the 'P' or 'F' but there may be a P or F else where in the cell so the color may come up incorrect. I'm trying to get it to just look at the first character in the cell. Can the 'IF' be used with CFs?
View Replies!
View Related
How To Restrict The Cell Data To Letters 'a' Through 'e' AND 'A' Through 'E'
I am trying to come up with an excel shee which can track the progress of a student on practice exams. This exam only has multiple choice questions, with answer choices A,B,C,D or E. What I am looking for 1) I want to restrict the cell data to A,B,C,D or E (both cases). Note that I am not looking for a drop down/combo box. It would take the user to use one or two mouse clicks to drop down the list, and I am not in favor of that. 2) I would like this validation/formula to be applied to all the cells in a particular column. 3) Nice-to-have. If the user enters a lower case letter, then I would like to convert to upper case.
View Replies!
View Related
Copy Data Cells Only In Selected Cell Column
I have a macro that loops through several workbooks and copies data to one common sheet. Works great with one flaw that I have not been able to resolve. I am using the End Property example below, this works great as long as there are more than one cell with data. With one cell having data this takes you to the bottom of the sheet. Range(Selection, Selection.End(xlDown)).Select Selection.Copy Need code to select from a specific cell to the last cell in that column with data where when the selected cell is the only one with data that would be the only cell selected.
View Replies!
View Related
Run-time Error '1004' : Select Method Of Range Class Failed
I have a userform that allows the user to view, modify or delete individual records (rows) of a worksheet in a workbook. If a particular item in a record is modified, a check occurs to ensure that the values for that item remain unique. All of that works exactly the way I need. The breakdown occurs when the records are then autosorted by a the values in a particular column. After the autosort, another column of values is copied and pasted to a different worksheet within the same workbook. For this to work properly, I must have the userform open along with the worksheet containing the records. If the userform is open with any other worksheet open, I get the following VBA error message "Run-time Error '1004' : Slect method of Range class failed". I need to be able to open the userform with any worksheet active and not experience this error when the autosort, copy/paste occurs. This is the code for the user form, the module for autosort, copy/paste and checking for unue values. This is the sub in the worksheet with the records 'This checks for unique values Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim LLoop As Integer Dim LTestLoop As Integer Dim Lrows As Integer Dim LRange As String Dim LChangedValue As String Dim LTestValue As String Dim smessage As String 'Test first 7 rows in spreadsheet for uniqueness Lrows = 8 LLoop = 2 'Check first 7 rows in spreadsheet While LLoop <= Lrows.........................
View Replies!
View Related
|
TRACKER
