NamaFile = Cells(2, 3).Value
NamaSheet = Cells(3, 3).Value
Windows(NamaFile).Activate
Sheets(NamaSheet).Select
Showing posts with label VISUAL BASIC. Show all posts
Showing posts with label VISUAL BASIC. Show all posts
Tuesday, March 6, 2012
Thursday, October 13, 2011
Find String in Cell
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Monday, January 24, 2011
Toolbar Enable
Sub reset()
Application.CommandBars("Cell").Reset
Application.CommandBars("Cell").enabled = true
End Sub
Application.CommandBars("Cell").Reset
Application.CommandBars("Cell").enabled = true
End Sub
Monday, January 17, 2011
Function get URL in Excel
Create in macro like function below:
Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function
then open excel, type in a cell "=GetURL(A1)" for link/url you will capture
after that, you can correction the URL using macro below.
For URL = 6 To 329
AddressName = Cells(URL, 23).value
Range("W" & URL).Select
Selection.Copy
Range("L" & URL).Select
ActiveSheet.Paste
Application.CutCopyMode = False
AddressURL = Cells(URL, 27).value
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=AddressURL, _
TextToDisplay:=AddressName
Next
Function GetURL(rng As Range) As String
On Error Resume Next
GetURL = rng.Hyperlinks(1).Address
End Function
then open excel, type in a cell "=GetURL(A1)" for link/url you will capture
after that, you can correction the URL using macro below.
For URL = 6 To 329
AddressName = Cells(URL, 23).value
Range("W" & URL).Select
Selection.Copy
Range("L" & URL).Select
ActiveSheet.Paste
Application.CutCopyMode = False
AddressURL = Cells(URL, 27).value
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=AddressURL, _
TextToDisplay:=AddressName
Next
Wednesday, June 2, 2010
Wednesday, April 14, 2010
Thursday, January 28, 2010
Menampilkan Nama File dan Tab di VB
'Menampilkan Nama File Excel di VB
SheetName = Worksheets.Parent.Name
'Menampilkan Nama Tab Excel di VB
TabName = ActiveSheet.Name
SheetName = Worksheets.Parent.Name
'Menampilkan Nama Tab Excel di VB
TabName = ActiveSheet.Name
Thursday, November 19, 2009
VBA Macro for Excel 2003 Export of Text File with Comma and Quote Delimiters.
VBA Macro for Excel 2003 Export of Text File with Comma and Quote Delimiters.
This code is a sample from the MSDN script library at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/html/odc_XL_Samples.asp
1. Open a new workbook.
2. On the Tools menu, point to Macro, and then click Visual Basic Editor (or simply press ALT+F11). In the Visual Basic Editor, click the Insert menu and then Module.
3. Type or paste the below sample code into the module.
4. Before running the macro, select the data that you want to export, and then point to Macros on the Tools menu and click Macro.
5. Select the QuoteCommaExport macro, and click Run.
Sample code:
Sub QuoteCommaExport()
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" & _
Chr(10) & "(with complete path and extension):", _
"Quote-Comma Exporter")
' Obtain next free file handle number.
FileNum = FreeFile()
' Turn error checking off.
On Error Resume Next
' Attempt to open destination file for output.
Open DestFile For Output As #FileNum
' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If
' Turn error checking on.
On Error GoTo 0
' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count
' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count
' Write current cell's text to file with quotation marks.
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";
' Check if cell is in last column.
If ColumnCount = Selection.Columns.Count Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount
' Close destination file.
Close #FileNum
End Sub
This code is a sample from the MSDN script library at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/html/odc_XL_Samples.asp
1. Open a new workbook.
2. On the Tools menu, point to Macro, and then click Visual Basic Editor (or simply press ALT+F11). In the Visual Basic Editor, click the Insert menu and then Module.
3. Type or paste the below sample code into the module.
4. Before running the macro, select the data that you want to export, and then point to Macros on the Tools menu and click Macro.
5. Select the QuoteCommaExport macro, and click Run.
Sample code:
Sub QuoteCommaExport()
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" & _
Chr(10) & "(with complete path and extension):", _
"Quote-Comma Exporter")
' Obtain next free file handle number.
FileNum = FreeFile()
' Turn error checking off.
On Error Resume Next
' Attempt to open destination file for output.
Open DestFile For Output As #FileNum
' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If
' Turn error checking on.
On Error GoTo 0
' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count
' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count
' Write current cell's text to file with quotation marks.
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";
' Check if cell is in last column.
If ColumnCount = Selection.Columns.Count Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount
' Close destination file.
Close #FileNum
End Sub
Wednesday, June 17, 2009
PR: 
LD: 
I: 
Rank: 
C: 
Tuesday, June 16, 2009
Menyisipkan Rumus Index dan Match di VB
Menyisipkan Rumus Index dan Match, gunakan sintaks berikut ini :
Cells(Line, 6).Value = "=INDEX(SOFTWARE.LIST!$A$2:$B$10000,MATCH(E" & Line & ",SOFTWARE.LIST!$A$2:$A$10000,0),2)"
Cells(Line, 6).Value = "=INDEX(SOFTWARE.LIST!$A$2:$B$10000,MATCH(E" & Line & ",SOFTWARE.LIST!$A$2:$A$10000,0),2)"
Sunday, June 14, 2009
Mengatur Tampilan Visual Basic
Untuk mengatur setting tampilan Visual Basic:
- pilih menu Tools > Options > Docking
- pilih Project Explorer
Thursday, June 11, 2009
Lokasi File
Set fs = Application.FileSearch
With fs
.LookIn = "C:\My Documents"
.Filename = "cmd*.*"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
With fs
.LookIn = "C:\My Documents"
.Filename = "cmd*.*"
If .Execute > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
Subscribe to:
Posts (Atom)