|
|
Hi,
Is there a VBA technique to search for existence of a range of cells containing certain values and if such range(s) exists severally in a worksheet return an array of these ranges or at least their coordinates?
Regards,
Cyril
|
|
|
|
|
Excel Ranges - Stackoverflow[^]
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
I'm not clear on what you want to search and for what, so perhaps you could clarify. Do you want to search
- a range of cells for a value that fits in one cell and return all the cells containing the value?
- a range of cells for a value that may occur across cells?
- a spreadsheet for a specific sequence of cell vales?
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Or maybe a rectangular range of cells, sized A by B, containing each and every value of another rectangular range of cells, sized C by D, where A*B equals C*D and the order of the matching cells doesn't matter.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
... contains the equivalent words, if translated to Dutch?
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Hi Luc,
What you describe is what I am looking to do, but the order of the matching cells matters. Cell A1 of the range has to be C1 etc... 
|
|
|
|
|
Hi Walt,
I think the 3rd is more of what I require. I am formatting reports of a system that dumps in ASCII format, and it places several reports sequentially on one text file. I figured to isolate these reports the best option is to upload into excel and search for the cells that contain the header fields and thus know where each report begins as the report offers no other fields/tags that indicate report start and end. Thus I want to iterate and find all ranges where cell 1 in the range contains value 1, cell 2 contains value 2, cell 3 contains value 3 etc thus indicating where each report begins. A bit messy but seems to be the only way out
Regards
modified on Wednesday, January 12, 2011 1:32 AM
|
|
|
|
|
I am attempting to write a macro so that within an Excel Worksheet I call it, and it saves the .xlsm, saves as a web page and then opens the ftp (in this case filezilla).
I am now stuck.
Where I want to go with this is that the ftpclient automatically opens on the correct website.
(We have several)
After that I am happy to select the files to upload and close.
I have embedded a hyperlink in the file at cell A1.
I suspect that I may need to somehow put the host/username/password into the macro somewhere and get it called on the call of the ftpclient.
Can anybody suggest where I am going wrong?
Sub Macro2()
ActiveWorkbook.SaveAs Filename:="C:\Users\David\Desktop\Book1.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = False
With ActiveWorkbook.PublishObjects.Add(xlSourceWorkbook, _
"C:\Users\David\Desktop\Book1.htm", , , xlHtmlStatic, "Book1_31795", "")
.Publish (True)
.AutoRepublish = True
Application.DisplayAlerts = True
End With
Application.Left = 161.5
Application.Top = 91.75
Range("a1").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
Hoi,
this[^] suggests one can invoke FileZilla client with a specific target website in mind, so if your VBA powers allow you to execute an arbitrary "DOS command", I'd go for it.
Alternatively, you could use a different FTP client, e.g. the one provided by (some versions of?) Windows, see here[^].
I expect those FTP clients will still prompt you for username and password.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
OK, as far as I can tell I need to open a dos shell and call a batch file.
Is that right?
Call the shell as:
Shell ftp.bat",vbnormalfocus
Batch file to look something like:
filezilla ftp://username:password@server:port
As far as I can tell this will open the command shell, then run the batch file ftp.bat which will call FileZilla and apply the necessary data to expose the website.
Is this right?
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
I don't know the details, as I avoid VB6 and VBA as much as possible. I don't think you need a batch file, although it could make sense to have it anyway.
I would read up on, and experiment with, things like this:
Call Shell("cmd /K FileZilla whatever_constant_arguments_you_want_to_provide" & or_variable_arguments, vbNormalFocus)
or maybe simply:
Call Shell("FileZilla whatever_constant_arguments_you_want_to_provide" & or_variable_arguments, vbNormalFocus)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
A word of caution:
Make sure filezilla.exe is in the PATH variable.
Or
You provide absolute path to the filezilla exe
Or
You place the bat file in the correct location with relation to how you have called the filezilla exe.
Shreekar
|
|
|
|
|
Thanks for the advice.
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
So i have dll project. In its code, i have a bunch of functions i use very often in many of my forms applications. One of them is a Bug Reporter class. When called, a dialog appears with some controls. When the user has filled all the textboxes and made valid combobox selections, you click the Submit button which submits the information to my email address. But this only seems to work sometimes. I can't figure out why.
I've made a simple forms application for others to try and test the Bug Reporter. I've posted it on another website and it only seems to work for some of my testers, others get errors. Here are some screenshots:
http://entrod.sp-website.net/images/bug.jpg[^]
http://img638.imageshack.us/img638/5442/96651911.png[^]
So im baffled. What can i do to make this work everytime? I mean, what's the point of having a bug reporter if the bug reporter itself doesn't work properly? 
Here my email code, if it's needed:
Private Sub bgwSender_DoWork() Handles bgwSender.DoWork
mErrorOccured = False
Try
mm = New MailMessage
With mm
.From = New MailAddress(tbEmail.Text)
.To.Add("My email address goes here")
.Subject = Me.Text
.Body = _
tbProblem.Text & ControlChars.NewLine & ControlChars.NewLine & _
"Bug type: " & cbbBugType.SelectedItem & ControlChars.NewLine & _
"Windows version: " & cbbWindowsVersion.SelectedItem & ControlChars.NewLine
.IsBodyHtml = False
End With
smtp = New SmtpClient
smtp.Host = "My SMTP server goes here"
smtp.Send(mm)
Catch ex As Exception
MessageBox.Show(ex.Message)
mErrorOccured = True
mErrorMessage = ex.ToString
End Try
End Sub
Virtual Space Shuttle Astronaut
|
|
|
|
|
Hi,
if by "it works sometimes" you mean it works all the time on some machines, and never on some other machines, then IMO the most likely cause is something is blocking your app's access to the mail server. It could be an AntiVirus/Firewall, or a (corporate) proxy server. Those would need extra settings, I don't have the details handy.
I expect you will never get it solved 100%, so what I would recommend is a two-way approach:
1.
try and solve it as much as possible. Figure out what needs to be done on Firewalls and proxy servers and document it.
2.
provide an alternate approach, i.e. when sending the mail fails, show a dialog telling the user your app created a file that he should mail to you.
FYI: each particular problem can be tackled by pasting the error message into the Google search box; whatever the error is, you're not the first to get it!
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Yeah it always works on some machines and never on others.
Usefull tips. I'll see what i can do.
But the email code itself, is that alright? Im not missing anything? Should i enable ssl? What about credentials/default credentials?
Virtual Space Shuttle Astronaut
|
|
|
|
|
I'm not an e-mail expert, so far I get specific problems solved, but I don't have the universal answer (unless it is 42). Your code looks fine to me, however I know there are many things that may interfere, credentials would be one of them. e.g. it may work better (or take less code) when the e-mail client happens to be running.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Maybe adding some credentials would help, it's a common problem.
smtp.Credentials = new System.Net.NetworkCredential(credentialUser, credentialPassword);
Cheers
sorry it's C#, translate to VB
If you can read this, you don't have Papyrus installed
|
|
|
|
|
It's the same isn't it?
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
The semicolon will cause errors
If you can read this, you don't have Papyrus installed
|
|
|
|
|
oooh, how did I miss that! :shame:
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC League Table Link
CCC Link[ ^]
|
|
|
|
|
CodeProject even has a smiley for you in your current situation: : - O yielding 
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
You didn't miss that, it was VB's mistake not to understand semicolons.
|
|
|
|
|
This code will only work if the computer where it is running can use the port 25. If this port is closed, then it will not work.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
