Change Code To Look Through A Range
Jun 5, 2013
This code is coulnm specific, can this code be adapted to look through a range for a match i write this code up to 25 time changing the column it looks at each time, the row always stays the same the location it paste to also always stays the same if it could be made to look through a range in reducing the amount of code to write.
the range it needs to look through would be, row 6 from column B to column Z.
the line i am hoping to adjust for this is below it reads: "WST.Cells(6, 2)Then"
HTML Code:
If WS1.Cells(8, 2) = WST.Cells(6, 2) Then
WST.Cells(7, 2).Resize(30, 1).Copy
Worksheets("torr1").Cells(9, 5).PasteSpecial Paste:=xlPasteValues
nextrow = nextrow + 1
End If
View 8 Replies
ADVERTISEMENT
Mar 27, 2012
I have recorded the code below that does what I need, problem is I will use the code on files with various amounts of rows. What can I change in it so it will work on all different size files.
Code:
Sub Macro3()
'
' Macro3 Macro
[Code]....
View 6 Replies
View Related
Jun 13, 2014
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.
Is there a way to do this with the VBA code.
View 3 Replies
View Related
Apr 3, 2014
I have problem to change text code into numerical code using macro. i have data contain text code and i would like to convert it into numeric . each text code has dedicated numerical code for example I have 4 fluids with text code text code:
FW = Fresh water
SW = Saline eater
CW = connate water
MW = Meteoric water
numeric code as follow
FW=1
SW=2
CW=3
MW=4
I would like to convert the text code into numeric code, it is easy if using excell , but it is routine job for me , i need to create macro to be more simple .
View 1 Replies
View Related
Sep 17, 2013
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet
[Code]....
View 8 Replies
View Related
Jun 16, 2007
Is there any way, in which i can make my code dynamic?? For example, in a code written in VBA, there is a reference to particular sheet, say "ABC". If i change the name from "ABC" to "ABCD" from excel, the underlying code should change automatically from "ABC" to "ABCD".
I want to make my code dynamic..
View 10 Replies
View Related
May 25, 2011
Trying to create a VBA code that will enter a formula such as: SUM(Q2:Qt) in a range of cells for instance A2:A10, and if there is information in B11, B12, B13 etc., it will consider that variable change and enter the SUM formula in cells A11, A12, A13.
I read that Dim LR As Long is the solution but not sure how to apply.
View 9 Replies
View Related
Feb 23, 2007
Is there a way that I can add a statement or change my code to automatically have the checkbox checked if a certain value in a cell is greater than zero?
This is my
If CheckBox1.Value = True Then Range("RANGE_WATER_AND_SEWER").PrintOut Copies:=1
If CheckBox2.Value = True Then Range("RANGE_ELECTRICAL_SERVICE").PrintOut Copies:=1
etc. Note: I have about 80 checkboxes on my form.
View 9 Replies
View Related
Oct 24, 2007
I have part of the code below and need to modify it so that others who only have C: instead of D: to use it
Filename = "D:TempBudgetMonthly.xls"
FileExists = (Dir(Filename) "")
If FileExists = False Then
msg = "The data file Monthly.xls does not exist !! Load the required file and run the report again !"
If MsgBox(msg, vbExclamation + vbOKCancel) = vbOK Then
End If
Exit Sub
End If
I need code to replace all D: to C: for the entire PROJECT not MODULE.
View 9 Replies
View Related
Apr 23, 2008
I know that there has got to be a post on here somewhere on this, but anytime I enter in "Import VBA Code" I get back loads of stuff that really isn't relevant.
In post [url] I had to change a line of code so that the formatting macros I made would be accessable to everyone.
Now that I have the fix in place I must import the fix in the one line of code into 100+ workbooks. I suppose I could do this manually, but I imagine that there is a much faster way to do this using VBA.
I was flipping through the "VBA and Macros for Excel" book by Mr. Excel and on page 334-335 I came across something that looked like it might work. However, it seems that this code is switching modules and my code is pasted in "ThisWorkbook".
All of the files are located in the same directory, so it would involve opening all the files in the specified directory, making the VBA code change, saving the file and closing.
View 9 Replies
View Related
Feb 25, 2009
I have a workbook with several sheets that get info from sheet"Main BOM". I use a =Main BOM(a6) type formula on the top row of other sheets where info is needed and I drag that down to the end of info on the Main BOM to get the info into other shhets of the workbook.
How would I alter this code to find the last row of data on the Main BOM tab and only drag down that far? Or would I need a completly user written macro? This is a recorded macro.
Sub DragDown()
'
' DragDown Macro
' Macro recorded 2/25/2009 by David D
'
Sheets("David,Jimmy--To Proto").Select
Range("A16:E16").Select
Selection.AutoFill Destination:=Range("A16:E47"), Type:=xlFillDefault
Range("A16:E47").Select
End Sub
View 9 Replies
View Related
Apr 20, 2009
I am trying to do is look at the first two digits of the number in column c (Range c5:c5000) if the number starts with 01 then I need the formula in column g = f5*12 if the number is 03 then I want the formula to be g=f5*24 I have several of these "cases" or "if" to put in can someone get me going a little further
Here is what someone gave me to work with but I'm having troubles getting it to work...I get a name error in the cell and I cant use this they way it is because the code needs to go into the sheet mod with a bunch of other code.Also when I use the Option Explicit the rest of my code wont work.
Option Explicit
Function re(Cl As Range, Src As Range)
Dim i As Long
Select Case Left(Cl, 2)
Case "01": i = 12
Case "03": i = 24
End Select
re = Src * i
End Function
=re(C5,F5)
View 9 Replies
View Related
May 26, 2006
Can Attached code be changed to alter font colour,type. rather than just fill colour.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("e:Az")) Is Nothing Then
Select Case Target
Case "Ph"
icolor = 6
Case "Li"
icolor = 12
Case "O"
icolor = 7
Case "An"
icolor = 53
View 3 Replies
View Related
Jul 4, 2007
This piece of code successfully replaces a module in a range of workbooks -
Sub CopyOneModule01()
Const strModName As String = "Module1"
Dim FName As String
Dim vWbkNames As Variant
Dim lLoop As Long
vWbkNames = Array("CFADS01", "CFADS02", "CFADS03", "CFADS04")
With Workbooks("CF266") 'copies from here
FName = .Path & "code.txt"
.VBProject.VBComponents(strModName).Export FName
End With...........................
View 2 Replies
View Related
Jun 18, 2008
I am currently making a spreadsheet that will calculate the costs of products that my company makes. I have created the code that will add a line for each component of the product and fill in the details, but i want to set it so that it run's when i change the part number field in the sheet.
The only way i know to do this is using the worksheet_change event, but i will have more than one of these 'calculators' on one worksheet and the part number field will not always be the same cell.
I thought that an IF statement would work, as the cell next to the part number will always be the title "Parent". This is the code that i have tried but i keep getting run time error 1004 application-defined or object-defined error when the IF statement would return TRUE, when the IF statement returns FALSE (the cell to the left is NOT "Parent") the code exits, which is correct.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Offset(0, -1).Value = "Parent" Then
Code.....
End If
View 3 Replies
View Related
Mar 12, 2009
I have an Excel workbook which operates as a template. When it is opened, it runs an OnOpen routine. When it is saved under a different name, is it possible to ignore the OnOpen routine if that file is re-opened? I suspect it could be done by testing the name of the file, but I can't figure out the code.
View 2 Replies
View Related
Sep 30, 2009
I have just noticed that there is no defult event for the running code after updating a label caption. I would like to have like a textbox does (Change,AfterUpdate). to run code on the label caption change. could it be done with activex?? can it be done at all.??
View 2 Replies
View Related
Feb 2, 2012
I have found the code below that is very good and changes the case size to Upper, Proper or Lower. The problem is that when I select a cell, range of cells, column or row it changes the entire sheet instead of the selected range.
Code:
Sub CaseSize()
Dim myCase, rng As Range, r As Range
myCase = Application.InputBox("Enter" & vbLf & "1 for Upper Case" & vbLf & _
"2 for Lower Case" & vbLf & "3 for Proper Case", Type:=1)
If (myCase = False) + (Not myCase Like "[1-3]") Then Exit Sub
On Error Resume Next
[code]....
View 9 Replies
View Related
May 7, 2012
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.
View 1 Replies
View Related
Apr 2, 2013
Please check this sample file. Say for example; I am sending this file via mail to different agencies. See in this file sheet named base & column title Agency , filters concerned agency wise and send via outlook to the concerned agencies mail id from mailinfo sheet. What my concern is since these are different agencies, I wanted to bring the agency name in the mail subject.
Link below:- [URL] ............
View 1 Replies
View Related
Sep 24, 2013
VBA code to do the following.
In F147 will go a value
Then if the value of F147 is greater than 20 the color of D144 would turn green.
View 3 Replies
View Related
Oct 31, 2013
I have a macro called pull that contains a year several times -see sample code below
I woulsd like a macro that will prompt and enable me to change the year from say 2013 to 2014 on the Macro called "Pull" . I intend to set up button to link to the macro
With Workbooks("Fruit & Veg Dept Commisions.2013.per Budget.xls")
With .Worksheets("Service")
Set cfind = .Cells.Find(what:=CDate(mmonth), lookat:=xlWhole)
.Cells(9, cfind.Column) = x
[Code] .....
View 2 Replies
View Related
Jan 14, 2014
This Code is Pasting "A1" into the destination - format and all. I need it to only paste in the value.
Sub test()
Dim lastrow As Long
lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Sheet2").Range("A1").Copy Destination:=Sheets("Sheet1").Range("A" & lastrow)
Application.CutCopyMode = False
End Sub
View 2 Replies
View Related
Nov 8, 2007
how would one change the actual code words - instead of using if statements i would like to have when statements that do exactly the same thing, i just want to write "when blah then blah"
View 9 Replies
View Related
Jan 22, 2008
I would like to have a vba code that changes the cell color so that if there is number 1 (just number 1) in some cell the background of the cell changes in to shade of grey and cell that has number 2 changes into darker shade of grey and so on. I have numbers 1-10 in random order in my sheet. I mean there is many cells that has the same number and the sheet is quite large so vba code would be ideal choise to do it quickly.
View 9 Replies
View Related
Feb 25, 2008
I am setting up VBA code to create different excel workbooks. One of the differences is in the VBA Tools References that must be enabled to make the whole thing function.
How do I write VBA code to turn on and off specific tool references ie. if i had a reference called OrbitCOM Library how can I turn it on and off using VBA code.
View 9 Replies
View Related
Apr 8, 2008
Say the amount is $10.25 the code (Loop shown below) writes it as Ten Dollars and Twenty Five Cents.
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
View 9 Replies
View Related
Sep 16, 2009
What is the key word sequence to cause VB code to execute on a worksheet change?
View 9 Replies
View Related
Jun 29, 2006
I'm trying to write a macro that will watch to see if there is change in a range of cells, and if the values of the cells change to preform a given action. I need to do it this way because I can't put a formula in some cells.
View 8 Replies
View Related
Jul 20, 2006
change the below code from a worksheet_change to a worksheet_calculate method. and still do the same action. the reason I am changing methods is due to the fact that the validation will not trigger the worksheet_change event to fire. this is my attempt to find an alternative way to fire off the macro.....
View 9 Replies
View Related
