VBA Forum RSS Feed

Delete entire ROW if a cell contains a phrase

Mon, 01 Dec 2008 10:53:56 -0700

I am trying to write a small piece of code for an Excel macro. I am working with some ladies here who use an excel spreadsheet to export some of the numbers from their system to their customer.

I need a quick macro that can examine each line of the .xls file and if a certain phrase appears in a cell (for instance, any cell in column C) then have it delete that entire line.

Additionally, if another macro (or maybe even an extension of the first macro, but it may be easier to have two separate macros) that could look at another row, like row FIVE, and if it contains all zeros for cells D-AA (or whatever it goes to) then I would like to have it remove that entire row if it has nothing but zeros from a certain cell over.

Any help would be greatly appreciated. I haven't had the privilege to work much with Excel with regards to creating macros and such.

Thanks in advance!

code is replacing value, but should adding instead

Tue, 18 Nov 2008 00:53:20 -0700

Hello,

This vba takes a code from sheet4 and searches the match through sheet2. If finds it, then copies the corresponding value from sheet4 to sheet2.
Sample:

Sheet4
(the code, col i) (the value, col K)
123456789 200

Sheet2

(the code, in one of the columns c, e:m) (the value, col AD)
123456789 value to here

And the code itself:

Private Sub searchMATCH()

Dim a, b(), i As Long
With Sheets("sheet4")
    a = .Range("i1", .Range("i" & Rows.Count).End(xlUp)).Resize(, 3).Value
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 1)
        For ii = 1 To 11
.Item(a(i, 1)) = a(i, 3)
        Next
    Next
    With Sheets("sheet2")
        a = .Range("c1", .Range("c" & Rows.Count).End(xlUp)).Resize(, 11).Value
    End With
    ReDim b(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        For ii = 1 To UBound(a, 2)
            If .exists(a(i, ii)) Then
                b(i, 1) = .Item(a(i, ii))
                Exit For
            End If
        Next
    Next
End With
Sheets("sheet2").Range("ad1").Resize(UBound(b, 1)).Value = b
End Sub

But the problem is, if there are more than 1 matching codes on sheet4(only) then the vba replaces the value with the last corresponding value.
But i need it to be added on to last value (+).

On sheet4: VALUE
123456789 200
123456789 300

Then it should be on sheet2 not 300 but 200+300 (eg 500)

Can anyone fix this?

Than you!

Sincerely,
Martin

writing VBA to make TAB cycle through text boxes

Mon, 17 Nov 2008 10:13:25 -0700

Hi all,

I have created several text boxes on my word document and wanted to have TAB cycle through them

It kind of workd, but the moment there is something in the text box, it stops working.

Can someone tell me what I am doing wrong:

Private Sub PROSPECTNAME_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Dim TxtBox As Object

If KeyCode = vbKeyTab Then

ActiveDocument.PROSPECTCODE.Activate

End If

End Sub

writing VBA to make TAB cycle through text boxes

Mon, 17 Nov 2008 10:13:25 -0700

Private Sub PROSPECTNAME_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Dim TxtBox As Object

If KeyCode = vbKeyTab Then

ActiveDocument.PROSPECTCODE.Activate

End If

End Sub

Cant find my error...

Fri, 14 Nov 2008 09:40:28 -0700

Hi i did try to figure out where i went wrong but i am not the best at this could some one assist me ?

-=-=-=-=-=-=-=-=-=-

ActiveWorkbook.Worksheets("Data").Activate

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

LSearchRow = 2
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

If Range("C" & CStr(LSearchRow)).Value = 1 Then

Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

Sheets("Status1").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

LCopyToRow = LCopyToRow + 1

For Column = 1 To 500
Sheets("Status1").Columns(Column).AutoFit
Next

Sheets("Data").Select

End If 'Closing of "Status1" If statement.

ActiveWorkbook.Worksheets("Data").Activate

Application.CutCopyMode = False

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:

If Err > 0 Then MsgBox "An error occurred, Error number: " & Err.Number & ": " & Err.Description

Exit Sub

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

could some one help m,e please?

thank you

WaLaSy

Excel - Dynamic COUNTIF Forrmula

Thu, 13 Nov 2008 08:10:06 -0700

I am not an expert VBA writer but can typically adapt codes to effectively provide the output I desire. However after two days I am admitting defeat.

My plans was to adapt this coding I found to:
For i = 1 To 10000
If Len(Range("J" & i)) = 0 Then
If Len(Range("I" & i)) > 0 Then
Range("J" & i).Formula = "=INT(RAND()*100)"
End If
End If
Next i

This is the formula I need to insert in the new blank column created next to column "I":
'=COUNTIF($I$2:$I$1941,I2)

But I have been unsuccessful because the range changes each time as the list will begin longer or shorter. Please help and I greatly appreciate your help.

Below is my entire VBA information if it helps.
Sub Delete_with_Autofilter_Two_Criteria()
Dim DeleteValue1 As String
Dim DeleteValue2 As String
Dim rng As Range
Dim calcmode As Long
Dim i As Integer

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Rows("1:2").Select
Selection.Delete Shift:=xlUp

'Fill in the two values that you want to delete
DeleteValue1 = "NULL"
DeleteValue2 = " "

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("I1:I" & .Rows.Count).AutoFilter Field:=1, _
Criteria1:=DeleteValue1, Operator:=xlOr, Criteria2:=DeleteValue2

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
Range("J:J").Insert Shift:=xlToRight
Range("J1").Select
ActiveCell.FormulaR1C1 = "Counter"
End With

'=COUNTIF($I$2:$I$1941,I2)

For i = 1 To 10000
If Len(Range("J" & i)) = 0 Then
If Len(Range("I" & i)) > 0 Then
Range("J" & i).Formula = "=INT(RAND()*100)"
End If
End If
Next i

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

Delete All Rows Except...

Wed, 12 Nov 2008 19:08:30 -0700

Hi All,

I'm new here.
I have a questions that i could not find the answer.
I'm also new in macro VBA programming.
The question is, I need to delete all rows in excel except the last row and any rows that contain this word "Blocked".
Anyone with brilliant idea?
Thanks :)

Word VBA : fields update

Thu, 09 Oct 2008 10:17:35 -0700

Hello

Hello

I've been working on a macro enabling a user to update a Word document linked to an Excel workbook. The macro open the Excel file if it closed, then the user can modify the Excel file before updating the Word document.

The code below works quite well approximately 9 times out of 10. Unfortunately, sometimes the following errors happen :
- the Excel file does not open
- the Excel file opens but it is impossible to update the fields
- the Excel file window is not activated, so the user thinks that it cannot open it (actually it is opened, but hidden behind the Word window).

Any ideas ?

Here is the code :

Sub Updatelinks

Dim xlApp As Object
Dim xlWB As Object

Set xlApp = CreateObject("Excel.Application")

On Error Resume Next

Set xlApp = GetObject(, "Excel.Application")

Set xlWB = xlApp.Workbooks("Champs automatiques.xls")

On Error GoTo 0

'Open the Excel file if it is closed

If xlWB Is Nothing Then

xlApp.AskToUpdateLinks = False

Set xlWB = xlApp.Workbooks.Open("H:\OBLIGS\Breve\Modeles\Cham ps automatiques.xls", ReadOnly:=True)

xlApp.Visible = True

End If

ActiveDocument.Fields.Update

End sub

Thank you for your help !

send active msword file to my vb application as mail merge

Tue, 07 Oct 2008 07:04:38 -0700

hi there,

wondering if anyone out there can help me with this. been looking high and low on the internet for a solution and doesnt seem to look like someone has done this before. I want to create a menu on msword toolbar to sort of like attach the active document to my vb app. possible?

appreciate any help.

-ejay56

Self-editing programs

Sat, 04 Oct 2008 16:19:32 -0700

I am new to programming, but I was wondering, how could I make a program that makes one random change to its programming based on a dictionary of commands and then reproduces? Thank you in advance for this somewhat odd request.

Search in excel.

Wed, 01 Oct 2008 05:55:01 -0700

Hi all.. im new here.. and was wondering if you can help?

im looking for some vb script for excel that will do a search.

if i explain you might understand.

sheet1 wil have a search box and button.

say i look for a contact, i enter his name click search.

then it searches sheet2 for the row with all the info.
then it displays it in a popup box in sheet one.

i have look every where i can can find and google can only search so much.

please help..

Refferer Crawler

Tue, 30 Sep 2008 10:46:16 -0700

where can i find this kind of code? or anyone can develop it?

Referer Crawler

the program is crawl, visit and leave a referrer url into server log.

thanks.

An slice of archiving code... trouble for me.. plz assist.

Sat, 27 Sep 2008 13:51:13 -0700

I am writing a piece of code for excel to back up a worksheet to a location before it runs another routine.

My problem is how to close the saved worksheet and activate the source worksheet.

Open_Name = ActiveWorkbook.FullName

My_date = Format$(Now, "mm-dd-yy")

Path = "C:\codetest\backup"

ActiveWorkbook.SaveAs Filename:=Path & "\dave-" & My_date & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

MsgBox ("File has been saved to " & Path & " as " & "dave-" & My_date & ".xlsm")

<><> the active workbook is now located in codetest\backup\dave-My_date
but I want to open and activate Open_Name and close Dave-my_date
please help. <><>

thanks for any help you folks can provide.

newbie needs help!

Thu, 25 Sep 2008 04:58:15 -0700

hi!

I'm trying to make an excel spreadsheet that can automatically hide rows when a cell value on that row is not equivalent to the value on a reference row. I'm not really that good with VB so I really do not know what to write on the macro

here is what I would like to happen..

let b1 = ref.cell

jump to cell c3

if c3=b3 then
move down to the cell bellow the current cell
else
hide current row then move down to the cell bellow the current cell

do this if statement while current cell<=bottom cell of the range

I hope I can get an answer here. I've been working on the spreadsheet for a week now trying to accomplish it without macros but until now I still haven't found any solution.

using macro is my only hope for it

thanks in advance

need help with sendkeys to a .exe program

Mon, 22 Sep 2008 02:37:34 -0700

My wish is to automate through Excel, where from I will be selecting numbers from a specified column of a worksheet. Those numbers will be decrypted by the program ABC.exe.
The 1st part of the code is to activate command prompt. Then from there I m initiating the program abc.exe. The window title changes from "c:\Windows\system32\cmd.exe" to "c:\Windows\system32\cmd.exe - abc.exe". But as abc.exe is initiated. Sendkeys is not working.
My code is as follows: (It's supposed to start execution with a button click from a worksheet)

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Dim WshShell As Object
Dim screen As Object
Dim cvc As Object
Dim strScrBatch As String
Dim strStandard As String
Dim psi As Object

Private Sub CommandButton1_Click()

Set WshShell = CreateObject("WScript.Shell")
WshShell.Run "C:\WINDOWS\system32\cmd.exe"
Sleep 20
WshShell.SendKeys "cd.."
WshShell.SendKeys "{Enter}"
WshShell.SendKeys "cd XYZ"
WshShell.SendKeys "{Enter}"
Sleep 50
WshShell.SendKeys "ABC.EXE"
WshShell.SendKeys "{Enter}"

strScrBatch = (Range("E" & 2).Value)
strStandard = (Range("E" & 3).Value)

SendKeys strScrBatch, True

WshShell.SendKeys strScrBatch
WshShell.SendKeys "{Enter}"

WshShell.SendKeys strStandard

WshShell.SendKeys "{Enter}"

MsgBox "Task Complete"

End Sub

Certificates

Fri, 19 Sep 2008 18:06:55 -0700

Hey, y'all,

I am having the worst time getting a certificate to work on my spreahsheet. Is there anybody out there who has worked with these?

Thank you!

PC

TCP/IP - How do I use the winsock or wsock32 DLLs?

Tue, 16 Sep 2008 19:01:49 -0700

OKIE DOKIE,

After quite a bit of poking and prodding my computer (and cussing and talking lovingly)...

Attached is the module, and this is the buttom I'm using for testing:

Private Sub CommandButton1_Click()
'ip.text is a text box that contains "126.125.81.3"
WinsockInit
MsgBox HostByAddress(ip.Text)
MsgBox HostByName(HostByAddress(ip.Text))
MsgBox InetAddrStringToLong(ip.Text)
MsgBox InetAddrLongToString(InetAddrStringToLong(ip.Text) )
WSACleanUp
End Sub

sooooo....how do I connect to anything and listen and talk to it? In case it matters, it is proprietary equipment; the data is not in any particular format or protocol I don't think. I can see it in Hyperterm. I just want the data raw for post-processing.

click image in web page

Tue, 16 Sep 2008 18:33:07 -0700

hi,

i already have vba to navigate to a web page. i just need some code to click on an image in the web page. i checked thhe source of the html, and it looks like:

<td><table id="Menu_4_i0" onmouseout="Menu.Out(this);" width="100%" rmCss="MenuItem" rmLab="View Reports" class="MenuItem" onmouseover="Menu.Over(this);" cellspacing="0" rmCssOver="MenuItemOver" cellpadding="0">
<tr onclick="goTo('/Reports/ReportList.asp', '')">
<td width="26"><img src="http://www.programmersheaven.com/Shared/Images/Icons/smallIcon_View .gif"></td>
<td nowrap class="MenuItem">View Reports</td>

so i am trying to figure out how i can use vba to click on the view reports image or item.

thanks in advance,
geebee

VBA and FTP server file transfer

Thu, 11 Sep 2008 04:10:26 -0700

Hi all,
This is my first post so do not criticize me to much :)

The system i am using is access 2007 VBA and is where the file i wish to transfer is generated.

I am having a large amount of difficulty transferring a file from a local drive to an FTP server.

I have all the relevant information i.e.:
server address
username
password

I have tried a few ways but all efforts have been fruitless.

Have tried copy but it does not allow me to enter the relevant user name and password and tends to crash regardless of this information.

Through the use of Code that operates using the Dos prompt.

The files that i wish to transfer are xml files for a website database and all i need to do is place the files on the server

Any comment or help will be much appreciated

Thanks

click link in web page

Mon, 08 Sep 2008 05:55:43 -0700

i have some code fills out a login screen and then results in landing on the splash/menu page. what i am having trouble coming up with is going from here to simulate clicking on a "view reports" link within the page landed on. attached is an "TRY" spreadsheet example containing my code. since the website is secure i cannot prodive its direct link or address at this time. any help would be GREATLY appreciated.

thanks in advance,
geebee

Attachment: TRY.xls (23040 bytes | downloaded 56 times)

Display data from rows to column using VBA

Tue, 02 Sep 2008 00:19:44 -0700

Hi i would really need some help here with VBA. I am quite new to programming. I have some data in excel that is display in rows. I want them to display in column instead. Any idea how i can do it? i did some coding but it only display the first line of code.

Initial Result:
John S1 Math 1
John S1 English 2
John S1 Chinese 3
Benny S2 Sci C
Benny S2 Hist D
Benny S2 Geo E
Ken S3 Bio C
Ken S3 Chem C
Ken S3 Phy A
Joyce S4 Lit A
Joyce S4 French E
Joyce S4 German D

Expected result:
John S1 Math A English B Chinese C
Benny S2 Math C English D Geo D
Ken S3 Bio C Chem C Phy A
Joyce S4 Lit A French E German D

How can i go abt doing dis? my coding looks like this:
Private Sub CommandButton1_Click()
x = 1
n = 1
i = 1

For x = 1 To 1
Sheet2.Cells(n, 1).Value = Sheet1.Cells(2, i)
Sheet2.Cells(n, 2).Value = Sheet1.Cells(2, (i + 1))
Sheet2.Cells(n, 3).Value = Sheet1.Cells(2, (i + 2))
Sheet2.Cells(n, 4).Value = Sheet1.Cells(2, (i + 3))
Sheet2.Cells(n, 5).Value = Sheet1.Cells((2 + x), (i + 2))
Sheet2.Cells(n, 6).Value = Sheet1.Cells((2 + x), (i + 3))
Sheet2.Cells(n, 7).Value = Sheet1.Cells((2 + x + 1), (i + 2))
Sheet2.Cells(n, 8).Value = Sheet1.Cells((2 + x + 1), (i + 3))
x = x + 1
n = n + 1
i = i + 1
Next
End Sub

What is the result of Application.names.count ?

Mon, 01 Sep 2008 01:51:41 -0700

I have searched but failed to understand the code below, anybody could kindly help:

i = 1 to Application.names.count and
name1 = Application.Names(i).name

What is the result of :

1) Application.names.count and
2) Application.Names(i).name

Thank you very much

File Access

Thu, 28 Aug 2008 02:53:15 -0700

Hi All

We have a folder 'WBInstance', which is permission denied for user 'xxx'.

Whether user 'xxx' the folder through vba code or passing someother login and password having access rights through vba.

Thank you in advance

Regards
Rema

FoxPro? (urgent help needed... pls? anyone?)

Sun, 24 Aug 2008 08:26:16 -0700

my teacher gave me an assignment that is about FoxPro. the problem that he gave me is to create a program for a video rental shop. this will include the "add-edit-delete record" commands. i have worked on some syntaxes but still am not able to make it work. and i also have to make a window that will generate another window and so on. i will attach what i have started on so far... help from anyone would be appreciated... by the way, im using a version 6.0 of visual foxpro. thanks so much...

SET TALK OFF
SET ECHO OFF
SET CONFIRM OFF
SET SAFETY OFF

USE VIDLIST *---[DATABASE]
INDEX ON LASTNAME TO VIDLIST
INDEX ON LASTNAME+FIRSTNAME TO VIDLIST

Clear

wait window "add/edit/delete record?" to nreply

Do case
case nreply $ "Aa"
do addrec
case nreply $ "Ee"
do editrec
case nreply $ "Dd"
do _delerec
case nreply $ "Pp"
do _printrec
end case
PROCEDURE ADDRC
Clear
vlastnme=space(15)
vfirstnme=(10)
vaddress=space(15)
vage=0
vgender=space(1)

@1.1 say "enter lastname : " get vlatname vfirstname
@2.1 say "enter firstname : " get vfirstname
@3.1 say "enter address : "get vaddress

read
wait window "do you want to save (y/n)? "to nans
if nans $ "Yy"
append blank
replace lastname with vlastname, firstname with vfirstname, age with vage,: address with vaddress
else
wait window "record not save"
return
*-----{procedure for editing record}

------------------------------------------------------------------------------------------------------------
procedure editingrec
set index to enrolist2
vlastname=space(15)
vfirstname=space(15)
@1.1 say "enter lastname to edit : "get vlastname"
@2.1 say "enter vfirstname to edit : "get vfirstname"
read
*locate for lastname=vlastname
seek for lastname for vfirstname
if found ()
wait window "record is active"......."
vlastname=lastname
vfirstname=firstname
vage=age
vaddress=address

@1.1 say "enter lastname : " get vlatname vfirstname
@2.1 say "enter firstname : " get vfirstname
@3.1 say "enter address : "get vaddress

read
wait window "do you want to save (y/n)? "to nans
if nans $ "Yy"
replace lastname with vlastname, firstname with vfirstname, address with vaddress
else
wait window "record not save"
and if
else
wait window "record is not found!......."
and if
return

*---------------procedure for deleting record--------------------------------------------------*

procedure _deleterec
set index to enrolist2
vlastname=space(15)
vfirstname=space(15)
@1.1 say "enter lastname to edit : "get vlastname"
@2.1 say "enter vfirstname to edit : "get vfirstname"
read
*locate for lastname=vlastname
seek for lastname for vfirstname
if found ()
nans=" "
delete
wait window "record is deleted!............"
and if
else
wait window "totally remove the deleted record(y/n)? ""to nans
if nans $ "Yy"
pack
wait window "record is deleted!............"
else
wait window "record is mark only for deletion......."
and if
else
wait window "record is not found!......."
and if
return

VBA Excel Macro for 'n x n' Matrix formation based on user inputs??

Thu, 21 Aug 2008 12:02:17 -0700

Dear All,

Can somebody help me out in generating a macro for forming a "n x n" matrix based on user inputs. - Excel sheet enclosed. Send me ur replies to reachspk@gmail.com :)
Inputs:

i j Value

1 2 1
1 3 1
1 4 -1
1 8 -1
2 1 1
2 4 1
2 5 -1
2 6 -1
3 5 1
3 6 1
3 7 -1
4 1 130.1459226
4 4 -160.8272092
4 2 -144.5355421
5 2 144.5355421
5 3 -183.6928125
6 2 144.5355421
6 4 160.8272092
6 5 137.3575476
6 7 152.8315505
7 2 144.5355421
7 4 160.8272092
7 6 137.3575476
7 7 152.8315505
8 3 183.6928125
8 8 172.7183057

In remaining locations, values are zero.

i j Value
1 1 0
1 5 0 etc.,

Here, we have 8x8 matrix. But, this should be generalized for forming "n x n" matrix

Please get me an Excel Macro for this.
Thanks in advance,
Prakash

Attachment: Matrix_formation_Inputs.xls (37888 bytes | downloaded 75 times)