One thing that absolutely drives me nuts is when I copy code letter for letter from the book (Excel VBA Programing For Dummies) and it doesn't work. You don't know if it's the book or yourself. Here's the latest piece of code that doesn't work.
Sub SetAlarm()
Application.OnTime TimeValue("7:30:00 pm"), "DisplayAlarm"
End Sub
Sub DisplayAlarm()
Beep
MsgBox "Wake up, homey!"
End Sub
The message and time are mine, of course, but the rest of the code is exact. I've changed the time multiple times, but it just flat-out won't execute.
I am looking for a very simple thing. A VBA code to split last letter of a column to the column after (offset 1). For ex. if K column contains "SEKWPRTY6" then "6" should be in L column and "SEKWPRTY" should stay in K column still. I tried this code but it gives "Invalid procedure call or argument"
VB:
Sub SplitAvail() Dim rng As Range For Each rng In Range("k1", Range("k" & Rows.Count).End(xlUp)) rng(, 2).Value = Right$(rng.Value, 1) rng.Value = Left$(rng.Value, Len(rng.Value) - 1) Next End Sub
I have code that I just noticed will not work if user inputs a capital S. I have tried a few things but I can not seem to get it to work. Here was the original code:
From an Excel 2003 workbook I generate some product labels. Each label has a 5 digit job number and a quantity that are passed to variables. If the quantity is 1, then I have no problems in printing my label. My problem is where the quantity is greater than 1. If a job has more than 1 item (can be up to 40 or 50) I need to add a series of letter (or letters) to the end of the job number.
For example:
Job number 12345 qty 1
number on label 12345
Job number 12346 qty 4
numbers on labels 12346A, 12346B, 12346C, 1234D
so that each printed label has a unique job number
I am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
I've written some code that passwords a sheet, and that code needs to be entered each time you access the sheet because you can carry out any changes.
I've written it so that the password changes every 30 days, and it's protected from the ctrl + break function.
The code will be activated after a certain date (28/02/09). Is it possible to add a bit of code to this, so that once the date has passed 28/02/09, it cannot be 'tricked' by changing the system clock to before this date?
I have a macro that runs through a list and stores elements to an array. If the element is not found in my list, it adds to the next available row and adds the value from the array to that cell. Right now, I am searching in column d but am storing the value of column a at that location. When I switch the reference to column D, the value is not inserted into my new list.
Here is my current code:
Code: Sub Arraytest() Dim arr As Variant, lastrow As Long, i As Long, f As Long, l As Long, lastrow2 As Long, insertrow As Long
[Code].....
Why does the code not work properly when I switch to D? The value is storing correctly in the array. I test this by the last line of the code and it executes properly.
Code: Sub Macro1() ' Sheets("Log").Select Range("F1").Copy For Each wb In Workbooks If wb.Name Like "Test Book*" Then wb.Activate Sheets("Data").Select Range("O2").Select ActiveSheet.Paste
[code].....
can't get it to work properly...Basically I want the code to copy the date in cell F1 of the 'Log' sheet in Workbook 'Main' - then goto an open Workbook called 'Test Book' (note: this Test Book is a partial string name used hence the other code around it) and paste the copied date into cell O2 in sheet 'Data'
Using this pasted date in cell O2 carry out the required filter function.
The copy/paste of the date from my Workbook Main to Test Book isn't working so the code then shows error when trying to filter the data using the pasted date.
the following code work on both PC and mac? It works fine on PC, but on mac the BeforeSave and BeforePrint code does not work and the SaveASheet code gets as far as opening and pasting the sheet but then is unable to save automatically.
Private Sub Workbook_BeforeClose(Cancel As Boolean) SaveASheet End Sub Private Sub Workbook_Open() Sheets("Open").Activate End Sub Sub SaveASheet() Dim fName As String Dim myPath As String Dim sht As Worksheet #If Mac Then..........
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
I've selected a case statement (see below) but I want to that code to work automatically. So i do not want to press a button to show a certain value in S2. But I want cell c2 to automatically pop up the value (depending of the value in V4). So kind like an if/then statement.
Sub CASEMEDEWERKER() Select Case Range("F4").Value
Case "Medewerker" Range("S2") = "M"
Case "Interview" Range("S2") = "I"
Case "Data" Range("S2") = "D"
Case "Observatie" Range("S2") = "O" End Select End Sub
I am trying to simultaneously use two Excel workbooks that are open at the same time--one is calculations--other is data scenarios...one worksheet = one scenario: A macro uses "Set" to specify workbooks for "current_wb" and "wbkFrom". Code here
Set current_wb = ThisWorkbook Dat_Fil = Application. GetOpenFilename Workbooks.Open Dat_Fil Set wbkFrom = Application.ActiveWorkbook
NOTE-Workbook object variables are declared outside any macro, and as "public." Code here
Public current_wb As Workbook Public wbkFrom As Workbook
In a second macro I want to use the Current_wb and wbkFrom object variables I set in first macro, but get the following error: "object variable or With block variable not set" for any of the following statements in the second macro
I've declared the object variables as public in a different code module where there is no other code; I've tried declaring as public at the top of the code module that has my macros, but declared at top of code module and outside any macro; I've tried declaring the object variables as "static" variables (instead of "public") within the first macro. I can't figure this out.
I had to remove a lot of pictures in the xls file in order to attach it, (since the original is about 5.3mb) but it worked out. When you move your arrow over the titles in column A a picture shows up thanks to a code. When you change the status in column G, the color changes together with the title in column A, as it should.
I would like the following code to be run for all those sheet/s that has a name = "single" (Not case sensitive neither an exact match) of my active workbook.
Code: Sub UIUIUI() Dim LR As Long, i As Long LR = Range("I" & Rows.Count).End(xlUp).Row For i = 1 To LR With Range("I" & i) If .Offset(, -1).Value = 1 Then .Value = .Value & "-" End With Next i End Sub
I am having trouble trying to develop a code to include into a spreadsheet for work. It will be a live spreadsheet accessed by a few people who will have control over there own columns in the spreadsheet (2 columns are designated for one project) Each Project director is to edit the info about their project.
So my goal is to put a code in cell C3 that shows the date that cells C4-32 and D4-32 were last updated.
I have a folder on a public drive. (\CFDIVIX1PublicPurchasing2009)
This folder contains multiple sub-folders (the sub folders are vendors which I purchase merchandise from) each sub-folder has 1 .xls file in it. Each file has 1 sheet containing all the information I need to retrieve.
Each one of these files has the same header information in cells A1 through I2 so I manually place that when I create this new workbook prior to inserting the macro. Each work sheet has a different # of rows depending on the number of items I buy from that particular vendor but I feel safe saying it never exceeds 250 rows per worksheet.
What I need For the macro to pull up the first work sheet and pull out the data from cells A3 through I250 and place that data into my new work sheet in the same format. Then I want the code to move on to the next worksheet and pull out the A3 through I250 and place that data in the new worksheet directly below the last row of data that was pulled in. (Also it would be great if the code ignored blank values. Like if the first work sheet it pulled data from only had data in the first 10 rows I don’t want 240 rows of blank rows before the next set of data)
I hope that running this macro will accomplish transferring all of my data from these multiple sheets into a single work sheet containing all of the pricing for all of my vendors. Once I have this I can place a second sheet on the workbook and use a formula to pull out pricing information that I need on a per basis without having to search through 50 vendors to find who it comes from. I don’t control the vendor work sheets that I want to extract data from so I need to use the macro to run it weekly incase my purchasing dept makes changes.
My company recently upgraded everyone to Microsoft 2010 from 2007 version. I have no substantial VBA skill and left with a VBA code which is supposed to extract a list of outlook emails sitting in a shared mailbox into Excel.
I was using that VBA code in Outlook 2007 and it worked fine but shows the following error when run in Outlook 2010: 'Run-time error '-2147221233 (8004010f)': The attempted operation failed. An object could not be found. Here is part of the code:
[Code] .....
It worked after one of the members suggested to "click on any line of this code and press F8 repeatedly until the yellow focus moves to the error line, don't press F8 anymore. Now in immediate window, copy paste each of below lines, press enter after each line. Let us know where the error occur." However, it stop working the next day.
I asked for a macro to delete the whole row if a duplicate customer number was found in column B. Sometimes, though, my column numbers change. So, logically thinking, I simply changed the criteria, but the macro ONLY seems to work if duplicate customer numbers are in column B only.
This code below won't work if the Customer Number is in column D instead of B even if the reference of B:B is changed to D:D, it doesn't carry the macro over.?
I have some VBA code which hides columns based on a cell value. The cell value changes according to which option button is selected. The code works but not when the button is selected and the cell value changes. It is necessary to click elsewhere in the sheet or press Enter to get the columns to hide. I want it to do it automatically as a user wouldn't know to click elsewhere.
The working code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("N5").Value = 2 Then Columns("O:R").EntireColumn.Hidden = True Else Columns("O:R").EntireColumn.Hidden = False
I have some code for changing the scale of my chart axes so that when new data is inserted into the column which the chart sources from (say column B), the axis scales on my chart will automatically adjust (as the Excel algorithm sometimes leads to charts which are a bit squashed). This code works fine, and I have included it below.
I have then tried to introduce an ActiveX listbox so that when you select an item in the box, the reference number generated (1,2,3...etc) then determines which data series is inserted into column B using a vlookup function. However, when I do this, the original VBA code for automatically adjusting the chart axes no longer works.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) With ActiveSheet.ChartObjects(1).Chart
VB: Sub openfiles()Dim Path As String Dim ExcelFile As String ' Path = GetFolder("C:UsersKinteshDesktop") Path = "C:UsersKinteshDesktopVBA programmingMaps" ExcelFile = Dir(Path & "*.xls")
[Code] ....
NextCode: GetFolder = sitem Set fldr = Nothing End Function
My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.
