Re: Worksheet_Change Event - Nothing is Happening
The change event does not work with cells linked to option buttons. You could assign a formula to another cell "=Z1" and then use the worksheet_calculate event, or assign an optionbutton#_click macro to each button.
Re: Macro to insert a blank row after every 2201 cells below
This assumes that your data starts in A1. Try with a backup
Sub This()
Dim a, b()
Dim i As Long, x As Integer, y As Long, z As Integer
With Sheets(1).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
a = .Value
End With
x = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row / 2200
If x > 1 Then
ReDim b(1 To UBound(a, 1) + x, 1 To 1)
For i = 1 To (UBound(a, 1))
If z = 2201 Then
b(y, 1) = Empty
b(y + 1, 1) = a(i, 1)
y = y + 1
z = 1
Else
y = y + 1
b(y, 1) = a(i, 1)
End If
z = z + 1
Next
End If
With Sheets(1).Cells(1, 1).Resize(UBound(b, 1))
.ClearContents
.Value = b
End With
End SubRe: TextBox Value to Another Workbook
Try this
Private Sub CommandButton1_Click()
Dim wkbk As Workbook
Dim txt As String
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
End With
txt = TextBox1.Text
Set wkbk = Workbooks.Open("C:\Documents and Settings\My Documents\sample.xlsm") 'change to your path and filename
wkbkB.Sheets(1).Range("I2") = txt 'change sheet and cell to suit
wkbkB.Close True
With Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
End With
End SubRe: Pull table from webpage with vba
Is this webpage public access? You could check the box with vba and then scrape the data. If I could look at the source code and play with it, I could help you better.
Re: Save As spreadsheet macro to different location
You should set it to true at the end of the code, and you are quite welcome.
Re: Save As spreadsheet macro to different location
use
Application.Screenupdating = Falseat the beginning. And
Application.Screenupdating = true
at the end.
Re: Save As spreadsheet macro to different location
What version of excel are you using? I use 2007 try removing or commenting out any of the lines that give you error. There may be compatibility issues.
Re: Save As spreadsheet macro to different location
Congratulations.
See if this gets you where you are going
Option Explicit
Sub SAVEAS()
Dim wkbk1 As String
Dim wkbk2 As String
Dim wkPth As String
Dim c
wkbk1 = ThisWorkbook.Name
wkPth = ThisWorkbook.Path
Workbooks.Add
wkbk2 = ActiveWorkbook.Name
Set c = Workbooks(wkbk1).Sheets(1).PageSetup
Workbooks(wkbk1).Sheets(1).Cells.Copy
Workbooks(wkbk2).Sheets(1).Cells.PasteSpecial , Paste:=xlPasteAll
Application.CutCopyMode = False
With Workbooks(wkbk2).Sheets(1).PageSetup
.LeftHeader = c.LeftHeader
.CenterHeader = c.CenterHeader
.RightHeader = c.RightHeader
.LeftFooter = c.LeftFooter
.CenterFooter = c.CenterFooter
.RightFooter = c.RightFooter
.LeftMargin = c.LeftMargin
.RightMargin = c.RightMargin
.TopMargin = c.TopMargin
.BottomMargin = c.BottomMargin
.HeaderMargin = c.HeaderMargin
.FooterMargin = c.FooterMargin
.PrintHeadings = c.PrintHeadings
.PrintGridlines = c.PrintGridlines
.PrintComments = c.PrintComments
.PrintQuality = c.PrintQuality
.CenterHorizontally = c.CenterHorizontally
.CenterVertically = c.CenterVertically
.Orientation = c.Orientation
.Draft = c.Draft
.PaperSize = c.PaperSize
.FirstPageNumber = c.FirstPageNumber
.Order = c.Order
.BlackAndWhite = c.BlackAndWhite
.Zoom = c.Zoom
.PrintErrors = c.PrintErrors
.OddAndEvenPagesHeaderFooter = c.OddAndEvenPagesHeaderFooter
.DifferentFirstPageHeaderFooter = c.DifferentFirstPageHeaderFooter
.ScaleWithDocHeaderFooter = c.ScaleWithDocHeaderFooter
.AlignMarginsHeaderFooter = c.AlignMarginsHeaderFooter
.EvenPage.LeftHeader.Text = c.EvenPage.LeftHeader.Text
.EvenPage.CenterHeader.Text = c.EvenPage.CenterHeader.Text
.EvenPage.RightHeader.Text = c.EvenPage.RightHeader.Text
.EvenPage.LeftFooter.Text = c.EvenPage.LeftFooter.Text
.EvenPage.CenterFooter.Text = c.EvenPage.CenterFooter.Text
.EvenPage.RightFooter.Text = c.EvenPage.RightFooter.Text
.FirstPage.LeftHeader.Text = c.FirstPage.LeftHeader.Text
.FirstPage.CenterHeader.Text = c.FirstPage.CenterHeader.Text
.FirstPage.RightHeader.Text = c.FirstPage.RightHeader.Text
.FirstPage.LeftFooter.Text = c.FirstPage.LeftFooter.Text
.FirstPage.CenterFooter.Text = c.FirstPage.CenterFooter.Text
.FirstPage.RightFooter.Text = c.FirstPage.RightFooter.Text
End With
ActiveWorkbook.SAVEAS Filename:="C:\Documents and Settings\Desktop\Book1_" & Format(Now(), "YYYYMMDD") & ".xls"
ActiveWorkbook.Close False
End SubRe: Automatically number a Column by Value
Assuming data starts in B2 put 1 in A2, then in A3 =IF(B3="","",IF(B3=B2,A2+1,1)) and fill down to applicable cells
Re: obtain data from 3 input with conditions
If used for similar application the function can be modified accordingly, yes. If you could attach a worksheet I could show you what would need changing.
Re: Excel VBA Debug Does Not work
I see. Can you access the code in the VBE?
Re: Excel VBA Debug Does Not work
When it stops use the mouse to drag the yellow arrow to the next line of code and then press F8. Does it allow you to step through the code this way?
Re: Excel VBA Debug Does Not work
You could put a random letter at the top of the code, or even use
stop
and then save and close the workbook. When you reopen the workbook it will error on the letter(delete the letter before stepping through code) or stop at the stop command (delete stop before stepping through code) depending on which you use. Then you can use F8 to step through the code and find where the problem is.
Re: Compare two columns from different tabs return value from third col of 2nd sht to
If you were to post a sample workbook, scrubbed of sensitive data, it would help someone to provide guidance. Also if you could attach a sheet with your anticipated results it would help.
Re: Auto fill Vlookup formula to Last Active Cell-Multiple Ranges
Please use code tags when posting code. http://www.ozgrid.com/forum/misc.php?do=bbcode#code check here for instructions on doing so.
You can define the range as
Range ("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
For columns with the same number of rows you can define a variable, such as
dim lastrow as long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
but if the columns have varying rows you would need to specify in each like the first line substituting the column letters accordingly
Re: obtain data from 3 input with conditions
forum.ozgrid.com/index.php?attachment/42170/Sorry, I thought I had saved the file before I attached it. The last changes I made were not in it. This should be correct.
Re: Search string in Ebay and copy Url of that page
try this variation
IE.Document.all("_nkw").Value = Sheets(1).Range("E2")
IE.Document.getElementsByTagName("input").Item(4).ClickRe: Save New Workbook Into a Specific Folder
try something like this
Dim Path As String
Path = ThisWorkbook.Path
wbkSecond.SaveAs Path & "/" & d & PrevMonth(Date), , , , FalseRe: Search string in Ebay and copy Url of that page
I would like to figure out the error you get before looping the code. Try inserting this additional line
IE.Visible = True
here
Set IE = CreateObject("internetexplorer.application")
IE.navigate "ebay.com"
IE.Visible = True
While IE.Busy
DoEvents
Wend
This will make the window with the ie.application visible. Step through the code and see if the ebay site is indeed opened and if the value in the cell is placed in the search bar. Then see if you get the same error. We may have to put a small pause after the page loads to be sure it is ready for input.
Re: obtain data from 3 input with conditions
forum.ozgrid.com/index.php?attachment/42164/A different approach. I used your existing tables and wrote a custom function. See attached.