|
Good Morning
I want to loop through all the available data in a column. Within the loop I am using an if statement to check for certain conditions. I then want to calculate the weightedaverage for the corresponding columns for all the rows that meets the if statement conditions.
Please see code below:
Select Case channel
'Channel 1 Threshold Check'
Case Is = 1
If ((Cells(i, 8).Value > Range("N10")) Or (Cells(i, 8).Value < Range("N10") And Cells(i, 6) > Range("N12")) Or (Cells(i, 8).Value > Range("N11") And Cells(i, 7) > Range("N13"))) Then
Range("N21") = conc1
Cells(i, 8).Font.Color = vbRed 'Colour the cells that meets the conditions'
Cells(i, 6).Font.Color = vbRed 'Colour the cells that meets the conditions'
Cells(i, 7).Font.Color = vbRed 'Colour the cells that meets the conditions'
ElseIf ((Cells(i, 8).Value < Range("N10")) Or (Cells(i, 8).Value > Range("N10") And Cells(i, 6) < Range("N12")) Or (Cells(i, 8).Value < Range("N11") And Cells(i, 7) < Range("N13"))) Then
Disc1 = Disc1 + 1
Range("O21") = Disc1
End If
|
|
|
|
|
Ehm, what's the problem?
I would say that doing this in VSTO instead of a macro would be soooo much easier, though.
|
|
|
|
|
Hi Kenneth
Unfortunately I am new to the whole programming thing. I am busy developing something in VSTO c#, where I am connecting to SQL pulling data into a dataset and then I want to loop through it. Unfortunately I need to get this done more urgently. I am using excel for most of my excel, but the sheets are massive and processing times are slow and seldom I run out of virtual memory.
Do you know how to calculate the weighted average within a For Loop with If statement?
|
|
|
|
|
weighted average? weighted against what exactly?
Average is easy, just add all values up, and divide by the number of values.
|
|
|
|
|
Is socket fast? Process we have is just simple data lock.
A main database which contains 10,000 - 20,000 records (created daily if MDB used) that will access the server.
There will be 100+ clients that will request what data they will access/process then send the keyed data to server and the server will save that record.
Socket is a very nice way to do this but I don't know if it can handle massive request. Server is only 8G RAM with I3 processor.
- Be who you are and say what you feel because those who mind don't matter and those who matter don't mind.
- Most of the problem in your life are due to two reasons: you act without thinking, or think without acting.
|
|
|
|
|
hansoctantan wrote: Is socket fast?
How fast is your network?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Sockets, with properly written code of course, is the fastest you're going to get.
The real bottleneck is going to be your database server hardware, database structure, quality of your queries and whatever code you write to send data back to the clients.
|
|
|
|
|
Using newtonsoft json to read a json file.
I download a sample json file with zip codes: http://jsonstudio.com/resources/
using this code:
Dim json As String = File.ReadAllText("C:\Users\Presentation\Desktop\zips.json")
Dim o As JObject = JObject.Parse(json)
But it fails with:
An unhandled exception of type 'Newtonsoft.Json.JsonReaderException' occurred in Newtonsoft.Json.dll
Additional information: Additional text encountered after finished reading JSON content: {. Path '', line 2, position 0.
Why would this default, and I assume correct data file, fail already here?
Can anyone help me? Thx
|
|
|
|
|
It doesn't work because you're not deserializing a complete JSON document. You're actually trying to is deserialize a list of JSON fragments. You have to process this one line at a time, not all at once.
Given the identifier names in the JSON file are very short, you'd probably be better off creating a strongly typed class so you can map those short names to something more friendly in your code.
The code snippets are in C#, but are EASILY converted to VB.NET. I'm just too lazy to do it for you.
private IList<ZipCodeData> ProcessFile(string filepath)
{
using (var sr = File.OpenText(filepath))
{
List<ZipCodeData> objects = new List<ZipCodeData>();
var lineContent = sr.ReadLine();
while (!string.IsNullOrEmpty(lineContent))
{
var obj = JsonConvert.DeserializeObject<ZipCodeData>(lineContent);
objects.Add(obj);
lineContent = sr.ReadLine();
}
return objects;
}
}
The ZipCodeData class looks like this:
public class ZipCodeData
{
[JsonProperty("city")]
public string City { get; set; }
[JsonProperty("loc")]
public double[] Location { get; set; }
[JsonProperty("pop")]
public int Population { get; set; }
[JsonProperty("state")]
public string State { get; set; }
[JsonProperty("_id")]
public string Zip { get; set; }
}
|
|
|
|
|
OK thanks. That explains. I was living with the impression that a json file could be read at once, because I saw that json conversion to dataset or datatable.
|
|
|
|
|
You can, if the file is written correctly. The one you linked to isn't setup as a proper JSON array of objects so you have to process them one at a time.
|
|
|
|
|
i now how it works with a user:
set WshShell = WScript.CreateObject("WScript.Shell")
WshShell.run "runas /user:'username' 'script.VBS'"
WshShell.AppActivate ("runas.exe")
but i want to put the clientcomputer instead of the user...
can you help me
|
|
|
|
|
You have to use WMI and the Win_Process class to start a process on a remote machine. Because of security risks, it can NOT be a user interactive process. The script you want to run must be on the remote machine so you will probably have to copy it over there first. Also, the person running your local script must have administrator permissions to the remote machine!
win32_process remote process[^]
|
|
|
|
|
I don't want to run it on a remote machine...
i want to start a script on a server and this script should run the other script on a normal pc... which is used by a user...
my problem is that when i run it on the user, my msgBox isn't shown anywhere, so I think i have to run it on the pc, isn't it right?
sry if my first question wasn't clearly...
|
|
|
|
|
You're not describing this correctly at all.
How many machines are involved. Give them names and use ONLY those names to refer to the machines. Do not use "user", use "Client" and "Server". Which machine is local and which is the remote machine?
Member 11916735 wrote: i want to start a script on a server and this script should run the other script on a normal pc.
OK. This describes a user logged into a server, running a script on it that will launch a script on a remote client machine.
What am I missing?
|
|
|
|
|
okay, please let me explain again:
I have one service running on the server (service is running as domain administrator).
this service executes my script.
this script should start another script on a client, wich prompts a msgbox for the currently logged in user.
Due the service (and so also the script) is running as domain administrator, I hope I can use its permissions for the client, no matter wich user is logged in on the client.
I hope everything is clearly for you now.
|
|
|
|
|
That's a LOT better!
Too bad, it's never going to work.
You can launch your process (script) on the remote machine. The problem is, because of security concerns, it's not allowed to put up any kind of user interface. The user on the machine will NEVER see the dialog box or anything that asks for input from the user on the remote machine. I think the security implications of code being launched on a users machine from an unknown remote server are quite obvious.
There is no way to get around this.
What's the purpose of this script running on the remote machine?
|
|
|
|
|
Starting the process as user will not work on Win2012. Getting access Denied and user is an admin
Dim psi As New System.Diagnostics.ProcessStartInfo
psi.UseShellExecute = False
psi.WindowStyle = ProcessWindowStyle.Hidden
psi.Verb = "runas"
|
|
|
|
|
For the last time, you do NOT need the Verb = "runas" line at all. It does nothing in this context.
In what context is this code running? Is it part of a Windows Service? A console app? A Windows Forms app? What?
The code works fine as an admin user. It will NOT work if the user running this code is Local System.
|
|
|
|
|
it's a console app and user is admin user and even if I remove Verb = "runas" still getting the same issue
|
|
|
|
|
OK, now, is the app that you're trying to launch on a network share? If so, Does the user that you're impersonating have access to that share?
What happens if you change the WindowStyle to Normal?
|
|
|
|
|
Maybe this details will help.
SID1 have access to cmd.exe and shares.
SID2 have access to DB .
When I run this I am passing SID2 while accessing cmd.exe under SID1
psi.Domain = Domain
psi.UserName = UserName
Dim pword As New System.Security.SecureString()
For Each c As Char In Password
pword.AppendChar(c)
Next
psi.Password = pword
Settings windows to normal don't help
|
|
|
|
|
No, you're NOT accessing "CMD" as SID1 and as you've been told before, you don't need CMD.EXE. The process you are launching is done as the user you're impersonating. If that user doesn't have access to the shares you're going to get Access Denied.
The process is not created as SID1. It's created as the user you're impersonating. A security context is first created for the user you're impersonating, then CreateProcessAsUser is called in that context to load the executable and setup the process. The problem is that the user you're impersonating cannot load the executable because it doesn't have access to the share where the executable is stored. Access Denied.
You're going to have to grant access to the network location to the user you're impersonating for this to work.
|
|
|
|
|
Upvoted for persistence.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
It's not the most accurate description of what happens behind the scenes, but it should be sufficient to get the point across.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
