Tagged Questions
0
votes
1answer
10 views
Trying to UPDATE INTO using SQL statement from Excel VBA without success
I am trying to insert a new row, populated with data, into a simple Access Table using SQL from Excel VBA and can't get over the first hurdle.
I have a single Table called "Test" with four fields;
...
0
votes
2answers
10 views
Expected end of Statement Error on Simple Formula Insert
###.value = "=LOOKUP(LEFT(W2),{"C","A","B"},{"Pick Up","Collect","Prepaid"})"
I want VBA to do this simple formula but getting "Expected: end of Statement" error.
It seems that I need to define ...
-1
votes
0answers
18 views
Excel VBA - Taking Row from one Range Variable and Placing it in another Range Variable
I have 2 Variables that are Range objects. In my code under an If statement I want to take a Row from the beginning of one range and add it to the end of another range.
Here is what I have: I need ...
0
votes
1answer
12 views
Excel 2010 VBA code to follow dynamic hyperlink
I have a worksheet with four columns A-D, column A has an ID number, B has description, C has location and D has a link to an image of the item. I have a short macro that ask the user for an ID# and ...
0
votes
1answer
20 views
VBA excel IsError function for special case
I'm looking for a function similar to the IsError function which returns TRUE if the input is fishy and FALSE otherwise:
I have two ranges rng1, rng2 and I want to perform this operation
IsError( ...
0
votes
3answers
19 views
Excel VBA - Cell(#,#) with variables
Im wondering if its possible to enter a variable that refers to a range into a Cell objects parameters. ie:
Dim visibleRows As Range
Dim visibleColumns As Range
Set visibleColumns = (G:P)
Set ...
1
vote
2answers
21 views
Listen to refresh events in excel
When the user does click on the "Refresh" / "Refresh All" button, excel seems to just call the refresh method for each (or selected) QueryTable(s) in the Workbook. However, listening to BeforeRefresh ...
0
votes
2answers
19 views
Macro populates wrong values - current date
I would like to ask for a help. My Macro was about to populate the current date to all populated rows. At the begining it seemed to work and suddenly it started to change (overwrite) the header to the ...
1
vote
0answers
18 views
Excel VBA Execute custom menu item when within a cell
I have added a custom right button click menu item to the context menu when within a Cell as per:
Private Sub AddTheMenuItem(myCommandBar As Office.CommandBar, caption As String, action As String)
...
-2
votes
0answers
23 views
Excel VBA Sort Populated ComboBox
What's the easiest way/code to alphabetically sort an already populated combobox in excel vba? I don't see a sort procedure as in regular Visual Basic.
0
votes
1answer
26 views
Making Few Row from a starting one
I give a simple example :
i have a row that is made up of 2 columns , each cell of this row may contain more than 1 word.
for each combination of words in each cell of the same row i have to obtain a ...
0
votes
3answers
23 views
VBA: Import Specific Variable Named Sheet in Workbook of Sheets to Access Table?
I'm looking for a way to import a specific tab into my Access table. Normally I do a TransferSpreadsheet type of code, so this is rather new to me.
I need the user to be able to open the file from ...
2
votes
2answers
25 views
VBA Excel handling ranges with for each
Quick question:
I set three ranges sourceRng1 and sourceRng2 targetRng. All equal of size.
I want to achieve this:
For each cell in targetRng
cell.value = sourceRng1 / sourceRng2
next cell
...
0
votes
1answer
7 views
Libre Office Calc time Function
I want to make a Excel File (Using Libre Office Calc) that calcuates the working time per day, but I'm struggling to get it right as I'd like to have it.
Example:
A1 = type in your Start time. eg: ...
0
votes
1answer
19 views
VBA How to check if a worksheet name is number
I have entered the following code in my VBA sub:
Sub copy2sheet()
Dim wkSht As Worksheet
For Each wkSht In Sheets
If IsNumeric(wkShrt.Name) Then
...
0
votes
0answers
16 views
Paste bidimensional array of Double values with 3 decimal positions lose separator and turn into thousands
I'm trying to paste a two-dimensional array of Variant/String into a range. That's no problem.
This Variant/String columns can contain strings or double values.
All works fine except in those cells ...
0
votes
0answers
28 views
Excel VBA is screwing up my PivotTables
I have a bunch of PivotTables that can be filtered by Month and Year. I wrote a UserForm and macro to let a user change the filtered month and year with just a few clicks of a button. Unfortunately, ...
-1
votes
0answers
23 views
Print dates between range
I'm currently using Office 2003 to create a rolling forecast that can automatically update and populate. Because of my lack of vba knowledge I'm currently stuck on how to do a few things- I could use ...
0
votes
1answer
32 views
VBA Runtime error 91 Object variable not Set
I'm trying to select a range with todays date only, but receiving Runtime error 91 on the Cells.find(TodayLast).Activate line. I can't understand what seems to be the problem.
Sub Escalation()
Dim ...
0
votes
1answer
33 views
VBA Excel: “yyyy” type string into date
Noob question:
how do I transform a "yyyy" string into a date?
My goal is to do the following
If "yyyy" < VBA.Date then
...
End If
-2
votes
0answers
20 views
want looping for sending mail thru excel vba to spwrate persins [duplicate]
i have tried below code to send mail thru excel but its loops only the range a2 again and again i need to loop until the active sheet please help.....
Sub Mail_ActiveSheet()
' Works in Excel 2000, ...
0
votes
1answer
28 views
Writing a macro for a spreadsheet in excel
I need to write a spreadsheet which when you press a button adds a row of data and asks for the parameters needed for the calculations, but I cant seem to get it right, its really frustrating me, any ...
0
votes
1answer
27 views
copy each sheet to separate cls files with path given
I would like to copy/save/export each of my sheets to separate vba file (which i would import later). I intend to do this because, i have a workbook beforeclose event to delete all the worksheets ...
0
votes
2answers
28 views
Why is my project calling another projects code?
Currently I have two versions of an excel addin - The released version, and the current WIP version.
When I load both into Excel and try to run the released code, it calls my WIP code instead, which ...
1
vote
1answer
18 views
How fill in the next row of a list in a spreadsheet using a macro?
I need to write a spreadsheet which when you press a button adds a row of data and asks for the parameters needed for the calculations, but I cant seem to get it to fill in the data in the next row ...
0
votes
1answer
36 views
Excel-VBA: how to improve that code (text in rows feature for a range)
I've a problem which I'm trying to solve at the moment.
It concerns converting formats in an excel file, which drives me crazy.
Import raw data from excel into another excel
Now I've found a way ...
2
votes
2answers
29 views
Excel - VBA : Simplify this If statement comparing cell to words
Easy question here.
I am currently using this in my program :
If LCase(inp_rng.Offset(1, 0).Value) = "street" Or LCase(inp_rng.Offset(1, 0)) = "ave." Then
score = score - 50
End If
It is ...
2
votes
1answer
66 views
Preference Votes Data to Groups
I have a problem creating a formula or VBA macro that sorts 'preference voting' data into appropriate groups for students selecting summer camp electives. Historically, we've done the voting and ...
0
votes
1answer
30 views
.Find method does not work
I am trying to use .find method to find a particular cell in the datasheet using the following code, which actually worked before, but I don't understand why it is not working this time...
Dim ...
1
vote
1answer
32 views
Folder Selection Dialog Box (VBA, Excel 2010) causing files to be unreadable
This may be quite a lengthy description, so please bear with me. The problems I am experiencing with Excel are related to custom macros, VBA, and file access.
Background: I am trying to write a macro ...
0
votes
0answers
9 views
How do I read IO_REPARSE_TAG_SYMLINK from file attributes in Excel VBA?
I have an FTP connection to a linux server in an Excel 2007 macro. I use FtpFindFirstFile to get a WIN32_FIND_DATA structure from which I can read .dwFileAttributes for a file, but I'm having trouble ...
0
votes
2answers
40 views
creating a loop in vba for an excel macro
I have recorded a macro in Excel that takes data from certain cells in one page of a spreadsheet and copies them to page in a different order. it does each cell individually, and is quite long.
...
0
votes
1answer
26 views
VBA With two columns if column 1 is a duplicate and column 2 is not give error
Example data:
Pin name voltage
V3V 3.3
V3V 3.3
V3V 3.3
V3V 4
VIN 58
VDR 6.5
VDREXT 6.5
V3V 3.3
desired output:
Bus Name voltage
V3V 3.3
VIN 58
VDR 6.5
VDREXT 6.5
...
0
votes
3answers
37 views
Copying Row Info from one sheet to another based on match
I have an excel book that has two sheets: 1) Import 2) Pricing Rules.
Pricing Rules Sheet
The A column is what I need to match on. Example values include STA_PNP4, STA_PST.. and others. There are ...
0
votes
1answer
36 views
Rolling Date Macro
I'm currently using Office 2003 to create a rolling forecast. I have a starting point and end point for a time interval (yr/month to yr/month) that I need to populate automatically at certain ...
2
votes
2answers
71 views
Select Case Loop
I modeled a Select Case loop based on another question I found on here(Link: Excel VBA Select Case Loop Sub).
The changes I had to do to make it applicable were pretty minimal, and I don't see where ...
-4
votes
3answers
41 views
Excel VBA - How to compare today's date in one column with a future date in a second column
I have two columns in a workbook; the first has today's date, the second has a date in the future. I would like to create a VBA that compares these two columns and verifies that the future date is ...
0
votes
2answers
19 views
Extract part of a string in excel cell
I have an excel spreadsheet in which there is a column containing a specific string. I need to extract part of that string in another column.
The data is as follows:
Column A
...
0
votes
1answer
38 views
VBA - Detecting a cell change within the context of a macro
I'm trying to create a macro that allows users to select a column and extend features of that column to other areas of the worksheet. I'm having trouble understanding how to make this work, though. ...
-1
votes
3answers
41 views
Delete entire row if value is greater than or less than? [on hold]
I am new to VBA for excel and I am stuck with a little problem. I have to delete an entire row if the value in column C is greater than 40000 or less than -40000 (these are data outliers). The list of ...
0
votes
1answer
18 views
VBA Excel: copy seperated cells
Quick question:
In Excel I have a sheet with the following structure:
Sheet1
A B C D E F G H I J K ...
2001 x x x x 2002 x x x x ...
-1
votes
0answers
42 views
Cataloging files by cell name Excel VBA
I'm trying to put data from a few CSV files together in one sheet categorized by name in the first column. I have around 1,000 different names in these columns - each name is assigned to a stock name ...
1
vote
4answers
48 views
Display 3 values in a message box with spaces between the values
I am trying to display 3 values in a MsgBox.
My logic statements are working, and it is picking the correct values I want - however I am unable to make my message box display 3 separate values with ...
-2
votes
1answer
35 views
How to locate a specific Tag whose position is not same in each excel files and read the measured value column for that specific tag
I have 1000 Excel files which I have narrowed down to 100, but in those 100 files my search has to look for a particular id tag.
Let me explain, each Excel file has different id tags, these tags ...
2
votes
2answers
47 views
Excel - VBA : identify pattern in words
I have a question I believe is quite simple, but I don't know the proper way to do it.
Basically, I would like my program to be able to identify words with a certain pattern in it, and if so, to ...
-1
votes
1answer
33 views
Extract and delimit data from text file into multiple Excel datasheets [on hold]
I am new to VBA and am trying to figure out how to properly extract data from a text file, delimit it, and import it into Excel 2010.
The issue is that the file is around 200 MB and exceeds the ...
0
votes
2answers
29 views
How do you specify a variable as a table name when selecting the headers of a table?
I am trying to create an Excel 2010 spreadsheet with a button that will create a new sheet and copy sheet 1 to it. I have everything figured out except that the sheets contain tables. When the sheet ...
-1
votes
1answer
27 views
Excel VBA Help w/ Type 13: Mis Match Debugging
I am working n a program where I need to store 4 individual values in one column and take the sum of 12 different columns so that I can have 4 totals over the past 12 months. The problem that I am ...
1
vote
2answers
30 views
Excel - Update Column with Date Time when Another Cell is First Changed
I would like my Excel spreadsheet Column B to be stamped with a date time only when Column A first changes. I have seen a lot of VBA code but it captures the last change, not the first change.
I ...
0
votes
1answer
29 views
Method name of object _chart failed
I am getting a Run Time error 1004 :Method name of object _chart failed while I try to name my chart. Sometimes this runs, and sometimes it doesn't. Here is the part that causes the error, (the whole ...
