|
Sort by supervisor and then loop through your cells. Concatenate the list of employees and when the supervisor changes you send the email and reset the variable storing the employees.
Pretty simple. Just think it through, perhaps write it down on paper and then translate to code.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Not sure I exactly follow - wouldn't a For Each or While loop work here? Not well versed in nested loops. Seems simple bu tI have yet to get anything to work.
|
|
|
|
|
Any type of loop will work. Again, just write it out on paper and then translate to code if you are stuck.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Trying this but keep getting a compile error "Next without For". Statements are lined up, not sure - breaking on Next l
Sub LoopUsers()
Dim l As Long
Dim j As Long
With Sheet1
For l = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If sSuper = "" Then
sSuper = .Cells(l, 7).Value
For j = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If sUser = "" Then
sGreeting = .Cells(j, 1).Value
sEmail = .Cells(j, 2).Value
sCC = .Cells(j, 9).Value
sUser = .Cells(j, 3).Value
sName = .Cells(j, 4).Value
sDescription = .Cells(j, 5).Value
sRole = .Cells(j, 6).Value
Else
sRole = sRole & ", " & .Cells(j, 6).Value
End If
If .Cells(j + 1, 3) <> sUser Then
End If
Next j
End If
If .Cells(l + 1, 7) <> sSuper Then
Call SendEmail
sSuper = ""
Next l
End With
End Sub
|
|
|
|
|
If .Cells(l + 1, 7) <> sSuper Then
Call SendEmail
sSuper = ""
Next l
There is no matching end If clause.
|
|
|
|
|
thank you, I added that in and it works, however not exactly the way I want. Loops through supervisor but not getting all users. 
Sub LoopUsers()
Dim l As Long
Dim j As Long
Dim k As Long
With Sheet1
For l = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If sSuper = "" Then
sGreeting = .Cells(l, 1).Value
sEmail = .Cells(l, 2).Value
sCC = .Cells(l, 9).Value
sSuper = .Cells(l, 7).Value
For j = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If sUser = "" Then
sUser = .Cells(j, 3).Value
sName = .Cells(j, 4).Value
sDescription = .Cells(j, 5).Value
For k = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
If sRole = "" Then
sRole = .Cells(k, 6).Value
Else
sRole = sRole & ", " & .Cells(k, 6).Value
End If
Next k
End If
If .Cells(j + 1, 3) <> sUser Then
End If
Next j
End If
If .Cells(l + 1, 7) <> sSuper Then
Call SendEmail
sSuper = ""
sUser = ""
sRole = ""
End If
Next l
End With
End Sub
|
|
|
|
|
Jughead3 wrote: Loops through supervisor but not getting all users.
It's not clear what your code is supposed to be doing, so I cannot make any suggestion. When you say "Loops through supervisor", what exactly does that mean, and where are the users?
|
|
|
|
|
I can't post worksheet so I explain in simplest terms
column A + B for email piece
column C = username (USER)
column D = name of user
column E = description (for email)
column F = role (user's role or roles)
column G = supervisor (of user)
I have code that loops through users and generates emails perfectly BUT let's say the supervisor has 20 employees; they would receive 20 emails in this scenario. I would like for them to receive 1 single email listing ALL of their users. This is what I mean by looping through the supervisor - getting their user data including roles.
Hope this makes better sense. Thank you.
|
|
|
|
|
Then you just need to loop through all the records making a list (or lists) where the supervisor is the same. Then use each list to send the emails.
|
|
|
|
|
Not that simple. Data is an extract - usernames can repeat along with supervisor. The unique piece (data point) is their role which is why this is in the code
sRole = .Cells(l, 6).Value
Else
sRole = sRole & ", " & .Cells(l, 6).Value
|
|
|
|
|
I am sorry bu that means nothing to me, as I have no idea what the structure of your data is. However, the issue is the same as most problems of this type. You must run through the data as many times as necessary to build the groups which have the common item (e.g supervisor, role etc) that you are interested in. The first thing to do is write down all the common criteria, and then decide on how to build the groups using those rules.
|
|
|
|
|
Hello !
I have a vb.net program.
In my program I want to use dates in the format "dd/MM/yyyy".
But when the windows date format is different I get errors.
For example if my program is executed in a Pc where the date is in the format : mm/dd/yyyy I get errors for example when day is more than 12 :
Dim dt As DateTime = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss")
I get error :
Conversion from string "20/09/2016 21:21:47" to type 'Date' is not valid.
What can I do to make my program work whatever the windows date format is configured.
|
|
|
|
|
That error does not happen where the date is formatted as a string. It happens when exactly the opposite happens: when the string is parsed to return a DateTime. At those positions, you must use DateTime.Parse("dd/MM/yyyy HH:mm:ss")
|
|
|
|
|
This is why all VB.NET programs should be compiled with Option Strict On.
You're taking a DateTime, converting to a String, and then assigning that String to a DateTime value.
Without Option Strict, VB is attempting to convert that String back to a DateTime. But it has to make some assumptions, one of which is that the String represents a date formatted using the current culture settings - in this case, US-format dates.
If you turn on Option Strict, you will get a compiler error telling you that you cannot store a String in a DateTime variable.
You could use DateTime.Parse or DateTime.TryParse to try to convert the String back into a DateTime.
But it would be much simpler to just not convert the DateTime to a String in the first place!
Dim dt As DateTime = DateTime.Now
Remember, a DateTime value does not have a "format" stored with it. It is simply the number of ticks since midnight on 1st January 0001. You only use a format when you are displaying the value to the user, or parsing a user-supplied string back into a DateTime.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello !
I'm using visual studio 2013.
In my app.config , there are 3 connections strings :
<connectionStrings><add name="db1Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="data source=PERSONALPC\MSSQL2008R2;initial catalog=db1;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
<add name="db2Entities" connectionString="metadata=res://*/Model2.csdl|res://*/Model2.ssdl|res://*/Model2.msl;provider=System.Data.SqlClient;provider connection string="data source=PERSONALPC\MSSQL2008R2;initial catalog=db2;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
<add name="db3Entities" connectionString="metadata=res://*/Model3.csdl|res://*/Model3.ssdl|res://*/Model3.msl;provider=System.Data.SqlClient;provider connection string="data source=PERSONALPC\MSSQL2008R2;initial catalog=db3;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
Now , I want to access these connections strings , using the following code :
Dim connstringtemplate1 As String = System.Configuration.ConfigurationManager.ConnectionStrings(1).ConnectionString.ToString
This string has "NULL" value
Dim connstringtemplate2 As String = System.Configuration.ConfigurationManager.ConnectionStrings(2).ConnectionString.ToString
This string has the first connection string
Dim connstringtemplate3 As String = System.Configuration.ConfigurationManager.ConnectionStrings(3).ConnectionString.ToString
This string has the second connection string
Dim connstringtemplate4 As String = System.Configuration.ConfigurationManager.ConnectionStrings(4).ConnectionString.ToString
This string has the third connection string
Also if I use this :
Dim nr As Integer = System.Configuration.ConfigurationManager.ConnectionStrings.Count
The value of nr is 5.
I have only 3 connection strings , so why there are 5 and the first is empty ?
This problem appear after I have installed the Update 5 on visual studio 2013.
Before everything was ok.
Thank you !
|
|
|
|
|
Why are you getting connectionstring by index instead of getting them by using their name?
|
|
|
|
|
desanti wrote: I have only 3 connection strings , so why there are 5 and the first is empty ?
They are probably getting merged in from the machine.config file. You can use
<clear /> in .config to get rid of them. c# - ConfigurationManager.ConnectionStrings returns extra connection string from machine.config - Stack Overflow[^]
But, as pointed out in the other response, the better option is to refer to them using a name rather than depending on their order.
ConfigurationManager.ConnectionStrings[connectionName].ConnectionString
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
First off, I'm sorry if this is in the wrong section  .I have this project that needed to receive data from PLC. The PLC comes from Keyence. Right now, I'm having hard time on how can I create it. Anyhelp will do. Thank you!
|
|
|
|
|
What sort of help are you looking for? Have you studied the documentation of the PLC device (whatever that is) to see whether you are accessing it correctly? Have you written any VB.NET code yet, and if so where is the problem?
|
|
|
|
|
Hmmm... where to start?
A PLC is a Programmable Logic Controller - basically an industrial computer.
So, how does the PLC make the data available - a direct connection or a networked connection?
Does the manufacturer have any code samples of how to connect to their device and get data from it?
We are generally willing to help, but your question is very wide open...
|
|
|
|
|
It's been a long time since VB6 has mystified me in its behaviour!
I have a function that solves Kakuro puzzles. It uses a 2D and a 3D array (both Integer) as its main data structures and employs a conventional DFS approach.
Naturally I compile to EXE with all run-time checks disabled for maximum grunt.
Recently I accidentally turned off these optimizations (I was in the wrong project window). But when I restored them and rebuilt the EXE I found to my horror that my solver has lost its mojo.
Every benchmark test I have reports exactly the same results (including, importantly, the number of DFS recursions) BUT all of them run 3 times slower than before. Nothing else has changed that I can think of.
I'm at a loss to explain this. Any ideas?
Mathimagics
|
|
|
|
|
You are hoping someone can give you advice on the performance of an unknown function in a dead language, compiled by an IDE that is more than a decade out of date, on an unknown OS.
I can only suggest the obvious - rebuild your function into a current language.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ouch!
Dead language? See this article!
Unknown OS? The VB6 IDE only runs on Win32 platforms, so my OS being Windows 7 should come as no surprise.
Unknown function? Guilty!
As it happens, I had already ported the function concerned to both C and F90, and was in the middle of benchmark testing the 3 languages (using GCC 5.3 for both C and F9).
The function is a Kakuro puzzle solver, a simple DFS model, making heavy use of a 2D array (grid) and a 3D array (cell domains).
For computationally-intensive functions like this, my experience is that optimised VB6 times should be comparable to unoptimised GCC, and hitherto I was getting times consistent with this.
When compiled with -O3, however, the GCC versions run 3 times faster.
So all hail the guys at Gnu. Even without the mysterious "mojo loss syndrome", C/F90 still romps home by a factor of 3.
Mathimagics
|
|
|
|
|
Dr Memory wrote: Dead language? See this article!
Yeah, it's DEAD, for about 15 years now. Pointing at some crybabies demanding MS update it or open source it doesn't make it any more live. That just proves the point even more.
Yes, I used to use VB6 way back in the day but that changed the moment I got my hands on pre-release .NET 1.0 and had to use Notepad to write code and the command line compilers to build it. Even in that primitive environment I saw what was coming and it was a lot better than VB6. I abandoned VB6 completely the day I got my hands on Visual Studio .NET.
The VS6 IDE is barely supported on Win7 and isn't supported at all on Vista and above, so the "only runs on Win32 platforms" doesn't exactly tell anyone what they need to know. Yes, people have gotten it to work on every version of Windows XP and higher, but only buy jumping through flaming hoops to do it.
|
|
|
|
|
As Dave said the article you referenced does not indicate anything but that the demise of VB6 is permanent.
As it has been almost 15 years since I was using it I would have no idea that it could run on W7. I consider leaving VB6 for VB.net and then c# one of my better tool decisions, Silverlight still pisses me off though
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
