Posts by AndrewJ

AndrewJ · Jul 24th 2007

Re: Macro To Match Data Line By Line

You mean if cell A1 is the same on both sheets AP and Move it means those lines are the same, and you want one of those lines (either, since theyre the same?) copied into match? If the lines dont match, just leave a blank line?

if I got that right:

Code

sub movelines()


dim x as long


for x = 1 to 40000


  if sheets("AP").cells(x,1).value = sheets("Move").cells(x,1).value then
    sheets("AP").rows(x).copy destination:=sheets("Match").rows(x)
    application.cutcopymode = false
  end if


next x


end sub

Display More

AndrewJ · Jul 17th 2007

Re: Column Selection

Something like this will get you started, I do a lot of list and data parsing also, your best friend is "worksheetfunction.counta"

Code

sub move_over()


dim x as long
dim y as long


for y = 1 to 10 ' your columns to process


for x = 2 to worksheetfunction.counta(columns(y)) ' will grab all active rows IF there are no blank rows in your data


cells(x,1).copy destination:=sheets("sheet2").cells(x,1)


next x


next y


end sub

Display More

Alternately, you could use a resize

Code

sub move_over()


dim x as long


x = worksheetfunction.counta(columns(1)) ' total used rows, if you have blanks in your data it wont get all of them


Range("A2").Select
Selection.Resize(x-1, 1).Select


Selection.copy destination:=sheets("sheet2").cells(2,1)


end sub

Display More

note I didnt test this, there might be a syntactical error(s) but the code works I've done these same things many times. You might have to mess around with the x-1 part of the resize, I think itll do what youll looking for but its easy to change. Just throw the selection.resize statement in its own sub, put real solid numbers in instead of variables, and play with how it changes the selected area.

edit: just saw that you might have empty rows in columns after the first one. Thats easy to fix:

Code

sub move_over()


dim x as long
dim y as long


x = worksheetfunction.counta(columns(1)) ' total used rows for column A, if you have blanks in your data it wont get all of them.  This value will be used for all columns in this sub


for y = 1 to 10 ' your columns


Cells(2, y).Select
Selection.Resize(x-1, 1).Select


Selection.copy destination:=sheets("sheet2").cells(2, y)


next y


end sub

Display More

AndrewJ · Jul 16th 2007

Re: View Groups On Protected Sheet

When protecting the worksheet, will it do what you want if you just leave the box "format columns" checked?

AndrewJ · Jul 13th 2007

Re: Dragging Formulas With Keyboard

Are you talking about fill? You can select the cell, drag down 10 or 100 or whatever cells, and then click ctrl-d and it will copy your formula down into all the cells you selected.

AndrewJ · Jul 12th 2007

Re: Send Email With Macro

"Sorry, my bad...I think the reason that I can't use CDO is because there are going to be people on many different servers using this. I know send keys isn't the most reliable but I'm stuck at this point. I was just wondering if there is anyway to tell excel that the email was sent and if not keep trying until it is sent."

Well, if theyre all running windows 2000 or later you shouldn't have a problem with it. I do something similar at my work - I have an application where when the user is finished and closes the excel file it sends a log file to me via CDO... this has worked on every computer at my work for 20+ people... and these people all have restricted user access on their PCs as well, and no problems at all. (I have the outlook method as the backup, which the log would make note of when used, but it hasnt had to be used once.)

AndrewJ · Jul 12th 2007

Re: Replace Multiple Hyperlink Addresses

Dangit, of course the experts have to come in and trump me

Good stuff tho guys!

AndrewJ · Jul 12th 2007

Re: Sending Email With Vba Loop Function

btw, unless youre setting 'Subj' to a value... you're not setting anything for the subject.

Try: "mysubjecthere" with quotes, dont do it as a variable

AndrewJ · Jul 12th 2007

Re: Sending Email With Vba Loop Function

Guys... stop with the sendkeys

Use CDO and you have no confirm message to click through.

AndrewJ · Jul 11th 2007

Re: Changing Multiple Hyperlinks With A Macro

Try something like this, as long as the username is the same for all the strings it should work

Code

Sub remove_chars()


Dim x As Integer
Dim y As Integer
Dim strAddress As String


For y = 1 To 10 ' starting and ending columns


For x = 1 To 1000 ' starting and ending rows


strAddress = GetAddress(Cells(x, y))


If InStr(strAddress, "C:\Documents_And_Settings\") > 0 Then


Cells(x, y).Hyperlinks(1).Address = Right(strAddress, Len(strAddress) - InStr(strAddress, "C:\Documents_And_Settings\") - 25) ' change 25 to the number of chars in the string to remove minus 1


End If


Next x


Next y


End Sub


Function GetAddress(HyperlinkCell As Range)
 If HyperlinkCell.Hyperlinks.Count > 0 Then
      GetAddress = HyperlinkCell.Hyperlinks(1).Address
 End If
End Function

Display More

AndrewJ · Jul 11th 2007

Re: Sending Email With Vba Loop Function

You probably need to add a reference to the Microsoft Forms 2.0 Object Library

That single piece of code shouldnt give any errors... what error was it?

AndrewJ · Jul 11th 2007

Re: Sending Email With Vba Loop Function

You can use CDO and avoid outlook entirely

Code

Dim iMsg As Object 
    Dim iConf As Object 
    Dim Flds As Variant 
     
    Set iMsg = CreateObject("CDO.Message") 
    Set iConf = CreateObject("CDO.Configuration") 
     
    iConf.Load -1 ' CDO Source Defaults
    Set Flds = iConf.Fields 
    With Flds 
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yourserverhere.com" 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' usually 25
        .Update 
    End With 
     
    With iMsg 
        Set .Configuration = iConf 
        .To = "[email protected]" 
        .CC = "" 
        .BCC = "" 
        .From = "[email protected]" 
        .Subject = ""
        .TextBody = ""
        .Send 
    End With 
     
    Set iMsg = Nothing 
    Set iConf = Nothing

Display More

AndrewJ · Jul 11th 2007

Re: Moving A Hyperlink

dude.. read my post:

"Without seeing it, I would say you're copying the row using ".Value". You should instead try ".FormulaR1C1"."

Try that and see if it works.

AndrewJ · Jul 11th 2007

Re: Extracting Data From Table And Emailing It As Attachment

Does this work? I didnt make it do an attachment or any fancy formatting in the string, but hopefully itll get you started.

Code

Sub Send_Mail()


'// Assumes the ten names are in column A, and data 
        ' starts in column B
'// Assumes you have selected the cell containing the 
        ' name you wish to extract data and send the email for


'// You'll need to add a reference to "Microsoft Forms 2.0 Object Library" 
        ' if you want to use the outlook method to send mail
'// in the VBE, go to tools > references, and then check the box and click OK.


Dim x as Integer
Dim y as Integer
Dim currentrow as Integer
Dim strData as String
Dim strName as String


strName = Selection.Value
currentrow = Selection.Row


For x = 2 to 100 ' or 2 to however many cols of data you have


 strData = strData & " " & Cells(currentrow, x).Value


next x ' when loop is finished strData contains all data in one string




'/// now, you usually have two options for emailing - outlook, and CDO


'/// CDO method (does not pop up the outlook "A program is trying to 
          ' send mail on your behalf" confirmation message.  CDO bypasses 
          ' outlook completely, and can be used if outlook is not installed.)


    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant


    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")


        iConf.Load -1    ' CDO Source Defaults
        Set Flds = iConf.Fields
        With Flds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yourserverhere.com"
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' usually 25
            .Update
        End With


    With iMsg
        Set .Configuration = iConf
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .From = "[email protected]"
        .Subject = "Report for:" & " " & strName
        .TextBody = strData ' this is the data string from above
        .Send
    End With


    Set iMsg = Nothing
    Set iConf = Nothing




'/// and here is the outlook method (just remember to remove or comment 
           ' out whichever method you're not using or you'll get two messages sent)


Dim OutApp As Object
Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "Report for:" & " " & strName
        .Body = strData
        .Send   'or use .Display
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing


end sub

Display More

AndrewJ · Jul 11th 2007

Re: Moving A Hyperlink

Can you post you code?

Without seeing it, I would say you're copying the row using ".Value". You should instead try ".FormulaR1C1".

If that doesnt work, then you may have to make the cell a hyperlink again after you copy the data over. Post your code and I'll look at it.

I believe the code for adding a hyperlink to a cell in VBA is

Code

ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:="url", TextToDisplay:="txt"

AndrewJ · Jul 2nd 2007

Re: Comparing Two Columns For Duplicates

errrr 'loop' is reserved btw, call the variable something else besides that name

I'm dumb sometimes

AndrewJ · Jul 2nd 2007

Re: Comparing Two Columns For Duplicates

No, C++ syntax will break your code

What I mean is, the logic behind it is the same - the commands are different but the thought process you should go through is the same.

I would do something like

Code

Dim FirstValue as string
Dim SecondValue as string
Dim loop as integer
Dim loop2 as integer


for loop = 1 to 100 ' or however many rows of data in 1st list


FirstValue = Cells(loop, 1).value ' first value from 1st list


for loop2 = 1 to 100 ' or however many rows of data in 2nd list


SecondValue = Cells(loop2, 2).Value ' 1st value from 2nd list


'// the logic part:


If left(FirstValue,1) = left(secondvalue,1).then
 ' this means they match, so you could set a variable to true
FirstCharMatch = True
end if


' then, change the strings so that they cut off the very first character


FirstValue = Right(Firstvalue, len(Firstvalue) -1))
SecondValue = Right(Secondvalue, len(Secondvalue) -1))


' and then repeat the If test from 10 lines above
' after testing all characters, test to see if your Match boolean variables are true
' ex: If FirstCharMatch = True and SecondCharMatch = True.... etc
' if you're looking for 80% or a fixed amount, you should increment an integer (i = i + 1) 
' instead of using boolean variables.  If, after testing all the characters, the integer is at or 
' above 80% of the length, then its a match and do whatever you want with your data






next loop2
next loop

Display More

I was assuming you have a basis for C++ and you'd know what boolean means and stuff like that, ask if it still looks like greek to you

AndrewJ · Jul 2nd 2007

Re: Comparing Two Columns For Duplicates

Why not just do it in C++? The logic should be the same....

You'd have to do something like test each character for an entry against each character of the other list and if 4 out of 5 match - or if 8 out of 10, etc - then count it as a match. I would use left() and right() to cut a string down by 1 character each time and compare it every time to the entry.

AndrewJ · Jun 28th 2007

Re: Disable Mouse

Yeah, very cool.

AndrewJ · Jun 27th 2007

Re: Protect Web Query From Viewing

Try adding a shape, and then right click on it and go to assign macro.

If you added the code into a new module in the VBE it should work...

AndrewJ · Jun 27th 2007

Re: Vba Listbox Lookup To Populate Another Listbox

Can you post the code you get the type match error in?

Posts by AndrewJ

Copy Matching Data From 2 Worksheets

Copy Data Less Headings

View Groups On Protected Sheet

Dragging Formulas With Keyboard

Send Email With Macro

Replace Multiple Hyperlink Addresses

Send Email With Macro

Send Email With Macro

Replace Multiple Hyperlink Addresses

Send Email With Macro

Send Email With Macro

Move Hyperlinks

Extract & Email As Attachment

Move Hyperlinks

Compare 2 Columns For Close Matches

Compare 2 Columns For Close Matches

Compare 2 Columns For Close Matches

Disable Mouse

Protect Web Query From Viewing

Vba Listbox Lookup To Populate Another Listbox