VBA - Insert And Rename Columns Not Working Properly
Feb 21, 2013
The code below is supposed to insert a column and rename it. However, when I debug, the code only renames the column, it does split or run the loop. I press debug again, and then code executes as it is intended.
I can't explain why I have to press debug twice for in order for the code to work properly.
Code:
Sub renameColumns()
With Sheets("byPosition")
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
.Range("E1").Value = "Exemption"
.Cells.EntireColumn.AutoFit
[Code] .......
View 2 Replies
ADVERTISEMENT
May 30, 2013
Total amount in Column B should same as shown in Column F
=IF(E3<>"",SUM(OFFSET($C3,ISTEXT(E3),,,1):C3),"")Above formula is calculating always two rows.
View 2 Replies
View Related
Apr 10, 2009
I have problem with vlookup. I am attaching my file.
View 3 Replies
View Related
Sep 3, 2013
I am including an attached file for reference.
The file is sorted A-Z, but if you scroll down towards the bottom, you will see that some values are out of order. Both instances of University of Central Florida and University of North Dakota are out of place.
University of Central Florida should be between University of California and University of Colorado.
University of North Dakota should be between University of Colorado and University of Southern Mississippi.
View 1 Replies
View Related
Feb 5, 2009
I have a really strange problem i have two different hard directories o: & u:
i have the following code linked to a command button
chdir "U: foldername foldername foldername"
saveactiveworkbook.saveas range("c9")
View 6 Replies
View Related
May 25, 2006
I try the following selection
Columns("B:AF").Select
Selection.ColumnWidth = 2.3
It is selecting columns a to af. Why would take column a if it is not part of select?
Sub Add_worksheet_month()
Dim Lst() As String
Dim I As Integer
Dim wname As Date
'Dim wmonth As Date
View 4 Replies
View Related
Nov 15, 2008
I am trying to work out an excel workbook for calculating family recipes. I have gotten most of it figured out but am having a problem with one thing. When the serving size of an item is 8 oz & the recipe calls for 14.5 oz the only way I can get it to work properly is to enter 1.75 in the serving size.
But I would like (actually need) to be able to enter the 14.5 and have it calculate the calories correctly - since sometimes the amount to be added to the recipe may be 15 ounces and the serving size is 8 ounces etc....
View 6 Replies
View Related
Oct 7, 2009
I have a rank formula that is seemingly erroring out on certain cells - indiscriminately. I have tried and tried to figure this out for myself but this is beyond my knowledge.
Only on some cells is the rank not working and returning a "-" (the error result).
I want the rank to use absolute numbers, so that it will rank according to variance or percentage (regardless of +/-). I am also using another cell (R1) to tell the rank what column to use.
As you can see there are a few entries, both + & -, that are not being included in the rank - for reasons beyond me.
View 8 Replies
View Related
Oct 25, 2009
I have 6 categories that need to be ranked 1-6 in order of highest number of occurances. My ranking formula is showing 1-7, missing number 3. I have attached a sample worksheet further showing what I am trying to explain. I need cell A3 to show a ranking of "3". Currently it shows "4". What am I doing wrong??
View 4 Replies
View Related
Jan 29, 2009
I have a form with 2 buttons and a frame. Inside the frame I have another button. I get a Type Error Message when I run the code and I don't understand why.
View 3 Replies
View Related
Jun 22, 2009
I have the following two codes on a userform, The first code performs two steps:
The first step is running the second code (MyDate), which is used for checking the entries in the userform textboxes and if there is any wrong entry a message box appears then it exits sub.
However, what happens when it finds any wrong entry is that it displays the msgbox and instead of exiting sub as it is requested in the (MyDate) code, it resumes running and moves to the second step and adds 1 to ComboBox1 ListIndex.
View 4 Replies
View Related
Apr 2, 2007
I am in the process of converting my programs over so as not to use these and I am already seeing a drastic difference in speed.
I have run into a problem that I need help with. I have a spreadsheet similar to the example below. The first column contains a list of college majors and after each major is a row of classes that are required by that major.
A25346 ENG101 MAT115 PSY150
A35678 ENG111 MAT115 SOC101
A23456 ENG101 MAT116 HIS201
The first thing I need to do is search down the first column for the major. Once the major is found I need to search across the row for the class. I am having trouble searching across a single row for the class.
Here is the section of code where I experience the problem
Set rMajor = wsMajor.Cells.Find(What:=Major, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rMajor Is Nothing Then
majorrow = rMajor.Cells.Offset(0, 1).Value............
View 9 Replies
View Related
May 25, 2009
I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.
Dim cnt1 as integer, cnt2 as integer,cnt3 as integer
cnt1 = 2
cnt2 = 50
cnt3 = 2
dim myrange as range
set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))
I have no idea why it won't work. I'm basically searching a columner range of cells.
View 2 Replies
View Related
Jul 13, 2006
I've been working on a particular spreadsheet and I've just noticed that
double clicking the format painter will only format one cell. It won't
retain the format to be put into the next selected cell which it should when
its double clicked. The same goes for copy and paste. Normally once you've
copied something you can paste it into a number of cells individually, one
after the other, but after the first paste selection, the paste option is no
longer available. I can't see any settings under options to fix this. What has happened, and how do I fix it? (I'm using Excel 2003 SP1 - is there a SP2?)
View 2 Replies
View Related
Oct 12, 2012
I thought I had been able to use array to have more than two criterias with Autofilter, but now I am unable to make the following code work. What I am trying to do is have all the records that does not contain either N/A, S/O or xx and also have a filter on column 125 for the value "OUI".
VB:
Sub test()
Set ws1 = ThisWorkbook.Sheets("SOMMAIRE_EN_ALL")
Set ws2 = Workbooks("Fichier_central_2013_anglais_2_CLEAN").Sheets("DETAIL_CONCAT")
Set r = ws2.Range("A1:du4783")
[Code] .....
View 3 Replies
View Related
Apr 13, 2014
In the attached sheet, one macro is developed which is not working properly. My req is to convert date from dd-mon-yyyy to yyyy/mm/dd format. (23-jan-2010--> 2010/01/23)
If incomplete date is there, then hyphen should place the gap i.e.
For date like APR-2014------> Output should be 2014/04/--
For date like 1998 -----> Output should be 1998/--/--
The below macro works only for row number 11, 15 and 16.
[Code]....
Attached File : date.xlsm
View 5 Replies
View Related
Jul 1, 2014
I'm using the following code to try to ensure a value is entered into a text box, but when I click over to the next text box skipping the first one entirely, I don't receive any message indicating the previous box is empty. I'm trying to make it so certain fields are required and others are optional. Here's the code I'm currently using:
[Code] .....
View 14 Replies
View Related
Jul 23, 2012
I have entered a custom sort order under 'first key sort order,' but the field is not sorting according to the list.
I'm sure I entered the list correctly, because I actually entered it a long time ago, and it was working for a while. However, today the underlying data was temporarily erased, and the pivot table, when refreshed, basically went blank. I restored the data and refreshed the table, but this field is back to sorting in the wrong order. Refreshing the pivot table and re-choosing the appropriate sort options do not work.
View 1 Replies
View Related
Jan 8, 2013
Had been using this formula for almost 3 years, recently the formula didn't work properly as the range goes down halfway only instead of to the last data in the column. I'm using Excel 2010 now.
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
View 2 Replies
View Related
Jul 16, 2012
I have a workbook contains 2 sheets, Admin & Master. I have a list (A:A) on Admin sheet having dates based on a drop down menu somewhere on other side on the sheet. I am running the below customized code that i found online to copy the Master sheet and then it should get renamed as per list (A:A).
Public Sub CopyRenameIt()
Sheets("Admin").Select
' Determine how many territories are on Admin sheet
FinalRow = Range("H65000").End(xlUp).Row
[Code]....
View 1 Replies
View Related
Oct 11, 2009
I've got a Workbook that contains the following worksheets:
1. conversion(2) - Hidden
2. Old Data - Hidden
3. Blank Form
I want to copy "Blank Form" once for each day of the month.
I'd like to rename each copied worksheet with the Date (i.e. Oct 01 2009)
Here's what I'm working with:
Sub Copysheets ()
Worksheets("Blank Form").select
Dim x As Integer
For x = 1 to 30
Worksheets(1).Copy after: =Worksheets(x)
Worksheets(x+1).Name=Format(DateSerial(2009, 20, x), "MMM-DD-YYYY")
Next x
End Sub
The problem I'm having is that the first sheet to be copied is the "Old Data" worksheet and the copy is renamed Oct-01-2009. After that the correct Worksheet is copied and renamed Oct-02-2009 and so on.
I can't figure out why it's copying the "Old Data" worksheet first.
View 9 Replies
View Related
Jun 24, 2009
Can I get a macro to insert a new worksheet each time it runs and rename it to a value held in a cell on another tab eg Sheet1, cell A1 - this value will change each time the macro runs so there wont be any duplicated tab names ?
View 9 Replies
View Related
Jul 3, 2008
I have a VBA macro I recorded that won't insert and copy columns into the proper areas. When I run this, It inserts every column all grouped together.
For example: I want to insert a column before column D, and give certain cells formulas. Instead, it inserts it before column C. The whole macro seems to, when ran, insert EVERYTHING before the range of data I am trying to split up.
Here is the
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/30/2008 by kmayfield
'
'
Range("C3:F3").Select
ActiveCell.FormulaR1C1 = "Jan-2008"
Range("G3:J3").Select
ActiveCell.FormulaR1C1 = "Feb-2008"
Range("K3:N3").Select
ActiveCell.FormulaR1C1 = "Mar-2008"
Range("O3:R3").Select
ActiveCell.FormulaR1C1 = "Apr-2008"
Range("S3:V3").Select
ActiveCell.FormulaR1C1 = "May-2008"
Range("W3:Z3").Select
View 9 Replies
View Related
Mar 23, 2012
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
View 2 Replies
View Related
Jun 30, 2014
I need to copy 120 columns from one sheet to a new. The columns needs to be in a certain order. So I need fx. from the original sheet column 2 is called "number" and I need that column to be put in new sheet as column 1 with new headline "no." Guess I need almost the same code for all of the just with different names etc.
I also need to put in blank columns with specific headlines in between some columns - so fx. in column 4 I need a blank column with headline "search"..
View 3 Replies
View Related
May 13, 2014
I have code for auto insert row, when i m getting to new row column Q formula not working
remove the auto generate serial number in column column A, only i want to insert auto with column Q formula to work.
find enclosed a worksheet for your ref.
Please see the below auto insert code;
Private Sub Worksheet_Change(ByVal Target As Range)
Dim O As Range
Set O = Range("O:O")
If Intersect(Target, O) Is Nothing Then Exit Sub
Application.EnableEvents = False
roow = Target.Row
Cells(roow + 1, 1).Value = Cells(roow, 1).Value + 1
Cells(roow + 1, 2).Select
Rows(Target.Row + 2).Insert
Application.EnableEvents = True
End Sub
View 1 Replies
View Related
Sep 20, 2013
I am using this macro below to paste pictures from a folder. This works perfect when run on excel 2007, however it only links image when this is run on excel 2010. How to eliminate the error.
Option Explicit
Sub alex_it_saving_lives()
'get the image folder path
Dim strImagePath As String
strImagePath = GetImageFolderPath
If (Len(strImagePath) = 0) Then
[Code] .........
View 3 Replies
View Related
Jul 14, 2008
I have an excel sheet where I need to insert the working days of the current month in a row. The working days are from Sunday through Thursday, that is, 5 days a week.
For example, for the month of July, 01 is Tuesday and the week ends on 3rd. So the row should look like this:
1-3 | 6-10 | 13-17 | 20-24 | 27-31
View 9 Replies
View Related
Aug 18, 2009
I have used the following code behind a projects Command button for some time, and works well in Excel 2002/2003.
It's used to look for and insert a Picture file, located on the users PC, select cell C2, re-size it to fit inside a bordered cell area and then nudge it over, off the border line.
Sub Load_Image()
Dim oPict, PictObj
Dim sImgFileFormat As String
'Open file
GetPict:
oPict = Application.GetOpenFilename("All Pictures (*.tif; *.bmp; *.jpg; *.gif; *.jpeg; *.png; *.cpt; *.tiff),*.tif; *.bmp; *.jpg; *.gif; *.jpeg; *.png; *.cpt; *.tiff")
If oPict = False Then End
Range("C2").Select
Set PictObj = ActiveSheet.Pictures.Insert(oPict)
With PictObj
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Width = 712#
.ShapeRange.Height = 510#
End With
PictObj.Select
With PictObj
Selection.ShapeRange.IncrementLeft 1#
Selection.ShapeRange.IncrementTop 1#
End With
Range("A1").Select
End Sub
Unfortunately, Excel 2007 doesn't seem to identify the Cell reference "C2".
It modifies the Picture size OK but does not position the picture in the correct position.
I've tried re-recording it but 2007 misses most of actions.???
Despite my efforts looking on other forums, I don't seem to find a code that works on both version of Excel.
View 9 Replies
View Related
Dec 30, 2009
I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times.
View 3 Replies
View Related
