Function Macro Debug: Continue Onto The Do Loop At The Bottom That Does All The Work
Aug 9, 2006
I am having a problem with a custom funciton I am trying to create. It will exit after it is finished with the IF Then Else statement. I need it to continue onto the Do loop at the bottom that does all the work.
Function UPCECheck( num As String) As Long
Dim CheckNum As Long
Dim TempCheck As Long
Dim X As Long
Dim Holdtxt As Variant
UPCECheck = 0
CheckNum = 0
Debug.Print Len(num)
If Len(num) = 12 Then
Holdtxt = num
ElseIf Len(num) < 12 Then
Holdtxt = "000000000000" & num
Holdtxt = Val(Mid(Holdtxt, Len(holdtext) - 12, 12))
End If...................
View 6 Replies
ADVERTISEMENT
Oct 29, 2009
We have had a macro running for a few years (Excel 2007 now, but started in 2003) that imports about 35 text files into separate sheets, creates a calculated SUM field for each sheet and copies that value to a title (or summary) sheet. We use this 2 to 4 times per year at inventory time, copying to a new file and deleting the old data before running the macro. The imported files initially create new workbooks, but the data is copied to the initial workbook into a distinct sheets for each file.
Recently (well, last August) this macro started hanging after importing (Workbooks.OpenText) a number of files, and not necessarily the same file every time (on repeated runs.) While trying to figure out the problem, I have now managed to get it to hang every time on the first file! However, if I am stepping through in the debugger it continues past the OpenText command and on F5:Run/Continue will then continue processing the rest of the files normally. If I delete the first file before running the macro, it then hangs on the second file instead.
Without debugging, the first file will import, display on-screen, and there it stops. If I put a break-point on the very next instruction after the import, that break-point is never reached. THINGS I'VE TRIED:.......
View 2 Replies
View Related
Nov 20, 2009
I'm trying to create vehicle maintenance inspection program for a mechanic at work. The trouble is the he's not fond of computer and I'm trying to make it as simple as possible. There's a debug in the line
View 2 Replies
View Related
Jan 29, 2013
In Visual Basic, the Continue For statement will complete the current iteration and start the next, if any.
Code:
For i = 1 to 10
...do some work
If [some condition] then Continue For
...do the rest of the work
End For
This statement gets flagged as invalid in VBA.
What's the proper way to end just the current iteration of a For loop in VBA without exiting the loop entirely (Exit Loop)?
View 3 Replies
View Related
May 13, 2014
I have attached a screencap of an example source table that I am trying to write a macro to manipulate each day. Starting conditions are that I have data in column A and B. Column A would be reference numbers that may be on one line, or more likely, on multiple lines repeating with varying counts. Column B is the weight associated with each line. I would need a macro that could loop through column A, determine if the ref number has changed, and then calculate/populate column C. Column C is the % of the total weight for the unique reference number. I have illustrated this in the table using column D. This is taking me incredible manual effort to complete right now and I do know VBA, I just don't know how to loop a variable range in this case.
View 9 Replies
View Related
Jun 5, 2012
I am trying to build a macro that will search a column for the word "TEST" and when it finds that word it needs to prompt the user with a YesNo message box (or something similar) if they would like to delete the entire row that contains TEST. If Yes is selected, it should delete the row and continue on to find the next TEST in the column and repeat this process. If the user selects No, it should continue on to find the next TEST in the column and repeat this process.
View 4 Replies
View Related
Feb 15, 2007
What I'm doing in this script is taking a whole bunch of variables and pasting them into a seperate sheet "Checks" which will construct a cashiers check. The problem is where I've highlighted the code in red. At that point I have copied the Vendor name to the clipboard - I then go to the "Vendor Info" page and search for that Vendor name. If that name exists on the page it works perfectly...moving one column to the right, grabbing the Address Line 1 and dropping it into the check, then going back and grabbing the Address Line 2 and dropping it into the check. The problem is when that Vendor name doesn't exist in the "Vendor Info" page...I want it to just paste two blank cells into the check (because I obviously don't have the address info for that Vendor)...but instead it gives me an error:
Run-Time error '91':
Object variable or With block variable not set
Sub Checks()
'
' Checks Macro
' Macro recorded 2/14/2007 by Derek Minner
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Selection.Copy
Sheets("Checks").Select
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
View 3 Replies
View Related
Jun 26, 2007
I have a macro that calls functions from another workbook.
For example:
Private Sub Worksheet_Change(ByVal target As Range)
If ThisWorkbook.OpenCommon <> -1 Then _
Application.Run Workbooks("common.xls").name & "!Arkusz1.CellChange", target
End Sub
But when I get an error in CellChange function from "common.xls" workbook, debugger points only to the third line of the code above, not to the bad line in called function.
Is there any way to change this behaviour (maybe some tool etc.)?
View 5 Replies
View Related
Sep 30, 2013
I tend to use a lot of For Each loops when I want to go through a range looking for something but this time I'm not so sure it will work.
I have 9 databases each containing over 400 rows with different pieces of client information, these databases are shared between around 40 people (I've also used the 'Allow users to edit ranges' utility to restrict access). What this means however is that the users cannot delete an entire row, instead they can only 'clear contents' on individual cells. This is fine until I try to generate statistics from the databases using autofilters which stop at the first blank row.
So I need to write a backwards for each statement that starts at the bottom and deletes all the blank rows on the way up to the top. I've tried similar things to this before using the For Each but when you use it to delete a row it sometimes skips the next row as it continues at the next one.
I've not managed to get it to work before, I'm just looking for a little guidance really as to how to do this correctly.
VB:
For TopRow = 1 To BottomRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row Step -1
'If is blank delete row.....
Next
View 1 Replies
View Related
Nov 17, 2008
I've been working on coding a summary sheet that pulls data from
worksheets in the active workbook.
I'm almost done, but am stumped at writing some code that will loop
through the columns that I have copied and total them. I recorded a
macro, but nothing happened when I ran it.
What I have now is this code snippet that I found which works
perfectly when I put my cursor in the active cell.
I am not sure how I should write it into my sub.
code that I found that works:
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
I tried this:
For Each col In NewSh
ColNum = ColNum + 1
Range(RwNum, ColNum).Select
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
Next col
View 9 Replies
View Related
Nov 7, 2006
I have a spreadsheet which is going to be populated everyday from figures from another spreadsheet using autofilters and the very useful SubTotal worksheet function. I'll attach a copy of the work in progress, also there will be a macro in here which Everytime the user presses update, it copies a range of cells and formatting and pastes it to the next available cell on the right. I've also drawn the movement of the cells i'd like The reason being, is that its going to give day to day, up to date figures from a datasheet.
The problem i have is that when i come to populate the grid which has been copied, i need a calculations to populate each cell in the Less<1week column, then when it gets to the bottom, move Back to the top, and accross to the right 1, and then populate down again, and so on until the end of the grid in question, i dont think i can use a for/next loop becuase it will be a different column address everytime. I dont really like loops, but i feel this needs it.
View 6 Replies
View Related
Apr 1, 2009
I tried to create a macro that compares two columns using a match function. The macro adds the macro function to the first cell and I double clicked the bottom right of the cell to have the formula auto fill down the column.
I noticed that the macro records the exact number of rows when you perform the auto fill or fill series. Now if I add another couple rows to the sheet, the step to auto fill all the cells in the column it stops before the bottom of the column.
I have been searching for a way to make sure the formula is placed in all the cells. I have been learning about loops but am having a problem building it.
View 9 Replies
View Related
Aug 13, 2009
Basically in B3 I have number 1. I want a macro to go to the next empty cell down and continue the series, i.e. 2. Then if it is ran again it will go to the next cell down and put in 3. This is what I want it to do only I don't want it to reference ranges. I added the offset line in but still need to know how to autofill to the nex line down..
View 2 Replies
View Related
Jun 11, 2009
I don't understand the relationships that are happening and what the final value would be if for example the PT was Red. I'm confused and was curious how four lines could all equal different values(highlighted).
UpUserPaint = UCase(RegPaint.Value)
Dim icount As Integer
LenRegPaint = Len(RegPaint.Value)
For icount = 1 To LenRegPaint
ValPAint = ValPAint + Asc(Mid(UpUserPaint, icount, 1))
Next icount
If PTRed.Value Then
ColorCompare = Abs(Pallet * (ValPAint + PrdtSeed - LenRegPaint))
ColorCompare = Abs( Round(ColorCompare / PrdtSeed - 7227, 0))
ColorCompare = ColorCompare And 6215971
ColorCompare = ColorCompare Xor 6215971.............................
View 4 Replies
View Related
Nov 24, 2013
I am trying write code withVBA to save separate file in a different folder with Loop.
Write a code with "loop" till it finds empty cell in the column and for every change in number a separate file needs to be saved in specified folder with file name as "10010, 10011,10012... and so on with data copied in the file saved.
Below is the data.
10010
10011
10011
[Code] ....
View 6 Replies
View Related
Feb 24, 2009
I have a workbook that contains 9 worksheets. Four of the worksheets have the same row lables in column B and must always be the same. Three of the worksheets are fed from the 4th sheet so that the integrity of the lables is maintained.
The user can change the lable value to meet their needs and they can insert or delete rows (within limits) as they see fit.
The attached macro "Sub Delete_Row_All_Sheets()" works fine when I run it from Debug (F8).
However, when I run it from Forms.ComboBox the macro returns to the "y = Application.InputBox("Enter The Row Number You Wish To Delete", _" screen. If I select cancel, the results I anticipated occur but I don't want the user to have to assume this will happen.
Why does the macro return to this screen when executed from the ComboBox but not when executed from Debug?
View 14 Replies
View Related
Apr 7, 2008
I am trying to open a xls file and convert into csv. My macro works when I'm in debug mode. but If i run the macro (Not in debug) mode then After opening a file control is not going to next function. What is problem? Even I am not getting any error too
sub open file (FileName as string)
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim RowNo As Integer
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
'Set xlw = xlx.Workbooks.Open(SourceFolder + "" + FileName)
Workbooks.Open FileName:=SourceFolder + "" + FileName
' Columns("F:G").Select
' Selection.Delete Shift:=xlToLeft
Save_in_WDrive (FileName)
Set xlx = Nothing
end sub
View 9 Replies
View Related
Apr 18, 2013
I want to a macro to start of with a Find method Eg Account..something like this
Cells.Find(What:="Account").Activate
Account could be in any row or may not be in the document.
My problem is when it is not in the workbook my macro will come up error. how to recode my macro to fix this.
Dim Finder as string
Set Finder = Cells.Find(What:="Account",...............
'Problem here
'
If Finder is not found then skip till next part of macro.
If it is then just select the Cell and I can work of that.
Also Can I do a Find formula to Find Either 'Account' Or 'Debt' Or Asset'?? Just want to know save me copy and pasting it down to change a name.
View 1 Replies
View Related
Aug 21, 2013
I have a macro that pulls in data from many workbooks. If there is no data in one of the workbooks the macro will not continue. Is there a loop or some other coding I need. The code for one of the worksheets is: (I am new to excel and know there must be parts that can be deleted in the below
Workbooks.Open Filename:= _
"J:SPORTSFORCESPORTSFORCE BUSINESS PLANSReporting spreadsheetCricket.xlsx"
Sheets("Numbers Report ").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Rows.Ungroup
[code]....
View 3 Replies
View Related
Nov 20, 2007
I have been able to put together a pretty hefty MACRO. I found out that some of the files I am importing may result in no data due to the manipulations I am doing. This causes a problem when I try to subtotal the data on each sheet. Is there a way to have the MACRO delete a sheet if cell a2 is blank and then continuw with the rest of the MACRO?
View 9 Replies
View Related
Feb 9, 2014
I'm trying to insert two rows in a subset of worksheets in a workbook by defining a range in a worksheet and loop through the worksheets in that range, but the code will only run on the active worksheet.
[Code] ..........
How I could get this loop to work?
View 1 Replies
View Related
Feb 9, 2014
I'm trying to insert two rows in a subset of worksheets in a workbook by defining a range in a worksheet and loop through the worksheets in that range, but the code will only run on the active worksheet.
Sub StatePIPData()
Dim sheet_name As Range
For Each sheet_name In Sheets("WS").Range("A:A")
If sheet_name.Value = "" Then
[Code] .......
View 1 Replies
View Related
Apr 14, 2014
LastRow = Worksheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row
For i = 2 To LastRow
Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
If (Worksheets("Sheet1").Range("AE" & i).Value < 1.5) And _
((Worksheets("Sheet1").Range("K" & i).Value > 0) Or (Worksheets("Sheet1").Range("L" & i).Value > 0)) Then
Worksheets("Sheet1").Range("AE" & i).Font.Color = 255
End If
Next i
My loop here works just fine, and my Formula works in testing. However when I move my formula inside the loop I get an error.
Is the syntax wrong in this line = Worksheets("Sheet1").Formula = "=IFERROR(+IF(+K2=0,0,+R2/(+IF(+K2>L2,K2,L2)*$AE$1/365)/P2),0)"
View 5 Replies
View Related
Aug 21, 2014
The for loop don't work in my excel 2010 macro. Probably the problem might be the Range("Fi") construct with the variable "i"?
Dim i As Integer
For i = 4 To 10004
Cells(i, 63).Select
Cells(i, 63) = Application.VLookup(Sheets(3).Range("Fi"), Sheets(4).Range("F:AY"), 45, False)
Next i
View 3 Replies
View Related
Aug 24, 2009
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
View 8 Replies
View Related
Nov 11, 2008
I'm comfortable with the IF statements in excel and not familir with many of the other functions available to me. I am trying to create a character calulator for Elder Scrolls III (Marrowind). So that a person may enter their race, ***, and other choices and their by calculating all starting stats. There are 10 races you can choose from and I can cover 9 of them with the if function (leaving one as if all other options are false). I would like to know if there is a better way such as a drop down list or some way to search an array or anything that would be better suited to search the race cell and return data if it's true. I'm running into the same probably with choosing the constalation (13 choices in this catagory).
I thought to split it two different data fields, by letting you choose from 4 in one cell and the other 9 in a different cell, but realized I don't know how to make it check two different cells for a choice to return it to the display cell without hitting the limitation of the 7 nested funcations in a forumla again.
View 3 Replies
View Related
Nov 13, 2008
I am doing a financial statement for a person but I did not create the workbook. The book has two sheets linked together. I am overwriting the names and numbers from last year's form. I have run out of lines on the first sheet and don't know how to copy and paste the macro to the bottom of the sheet so that I can carry on with inputting my numbers. Here is a sample of the workbook.
View 4 Replies
View Related
Apr 25, 2014
i checked and checked and my IF function just gives me the wrong answer... attached is just a sample data..i have over 230 lines to check actually..
View 4 Replies
View Related
Nov 21, 2008
I can't get the text box function to work on this spreadsheet.
View 2 Replies
View Related
Jul 9, 2014
Possible to create a macro that would detect end page (jumping from page 1 to page 2 for instance) and add a bottom border at the last row of the page.
I have attached a sample where I highlight in green the end of the page and added manually the bottom border (I did it only for the first end pages).
View 7 Replies
View Related