excel - VBA Testing two values, if one is different, copy -


i having fair amount of trouble code below:

sub testemail()      dim long     dim lastrow long     dim worksheet     dim b worksheet     dim strtext     dim objdata new msforms.dataobject     set = workbooks("book2").worksheets(1)     set b = workbooks("book1").worksheets(1)     lastrow = a.cells(rows.count, "a").end(xlup).row      = 2 lastrow         if not iserror(application.match(a.cells(i, 7).value, b.columns(3), 0)) , iserror(application.match(a.cells(i, 4).value, b.columns(11), 0))             a.range("d" & i).copy             objdata.getfromclipboard             strtext = replace(objdata.gettext(), chr(10), "")             b.range("k" & ).value = b.range("k" & ).value & " / " & strtext         end if     next  end sub

i face 2 problems, 1 has me stumped , other due lack of knowledge:

the line after if supposed check if 2 values (numbers) in both workbooks match, , if 2 other values (text) don't match. if true, must copy value book2 , add cell in book1.

the problems are:

-the macro doesn't seem recognise when values match or not.

-in last line before "end if", don't know how tell excel copy text cell didn't match in second check.

i sorry if not clear enough, hard explain.

i'm hoping 1 of experts knows how make work.

thanks in advance

  • you using if not condition 1 , condition 2, saying if doesn't match both conditions, run code. want make nested if statements however, 1 if , other if not
  • to copy missing i after "k"&: b.range("k" & i) = b.range("k" & i).value & " / " & strtext
  • the address of cells inside range function, in case be:

//it cell of email first workbook tou copying, input column d

a.range("d" & i).copy

//add workbook b in column k value cell k#/value copied

b.range("k" & i) = b.range("k" & i).value & " / " & strtext

you can make this: b.range("k" & i) = b.range("k" & i).value & " / " & a.range("d" & i)

this way matching lines, if ids on same rows on both workbooks work. if aren't, have use nesting loops or .find function

edit:

  • if understood it, code below might work if make changes application, because didn't have data test , columns, etc. try implement it.

lastrowa = a.cells(rows.count, "a").end(xlup).row lastrowb = b.cells(rows.count, "a").end(xlup).row = 2 lastrowa     'address of string      lookforstring = a.worksheets(1).cells(i, 4) '4 column_index     'range on workbook     a.worksheets(1).range("d1:d" & lastrowa) 'choose column         'function .find string on book      set mail_a = .find(lookforstring, lookin:=xlvalues)         if not mail_a nothing         firstaddress = mail_a.address             ' actions here                'range on workbook b                b.worksheets(1).range("k1:k" & lastrowb) 'choose column                     'function .find on workbook b                    set mail_b = .find(lookforstring, lookin:=xlvalues)                      if not mail_b nothing                      firstaddress = mail_b.address                          'actions                          'verify if 2 other values (text) don't match                          if not write_match_condition_here                             'no need verify of equal because .find function used same reference                             'i use .cells .row , .column show way , make dynamic                             b.cells(mail_b.adress.row, mail_b.adress.column) = b.cells(mail_b.adress.row, mail_b.adress.column).value & " / " & a.cells(mail_a.adress.row, mail_a.adress.column) 'choose columns                          end if                      set mail_b = .findnext(mail_b)                          loop while not mail_b nothing , mail_b.address <> firstaddress                      end if                end                 set mail_a = .findnext(mail_a)             loop while not mail_a nothing , mail_a.address <> firstaddress         end if     end       next      end sub

p.s.: <> missing on mail_a.address <> firstaddress , mail_b.address <> firstaddress, when posted 

     

 






























    





Comments











Post a Comment



















Popular posts from this blog









android - InAppBilling registering BroadcastReceiver in AndroidManifest
-





















    i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample:   // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases().   currently there's class extends broadcastreceiver :   public class iabbroadcastreceiver extends broadcastreceiver { /**  * intent action receiver should filter for.  */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener;  public iabbroadcastreceiver(iabbroadcastlistener listener) {     m...








Read more










python Tkinter Capturing keyboard events save as one single string -





















    is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable.   from tkinter import *  root = tk()  def key(event):      text= event.char      text+= event.char      print ("pressed", text)  def callback(event):      frame.focus_set() print ("clicked at", event.x, event.y)  frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack()  root.mainloop()          currently, creating text  variable , everytime key()  function gets called, , text stores last character typed.   you can define text module level variable , , use module level text  inside key  function -   text = '' def key(event):     global text     text+= event.char     print("pressed", text)       








Read more










sql server - Why does Linq-to-SQL add unnecessary COUNT()? -





















    here tables:   create table customer  (     cid int primary key,     name varchar(32) )  create table ord  (     oid int primary key,     cid int foreign key references customer,     address varchar(20) )   here's linq-to-sql statement:   var aaa = c in db.customer           select new { c, o = c.ord.tolist() };   and here's inexplicable query generated linq-to-sql:   select      [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid],      [t1].[cid] [cid2], [t1].[address] [address],      (select count(*)      [dbo].[ord] [t2]      [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid]   i want understand how rid of count(*)  part. it's entirely uncalled for!          linq needs count(*)  in order determine number of entries returned o = c.ord.tolist() .       








Read more