Attention VBA/Excel experts

Discussion in 'Coffee Table' started by alexenposadas, Oct 10, 2013.

  1. alexenposadas

    alexenposadas Member

    I'm praying for help to accomplish something (VBA/Excel related).

    Here's a brief description, so ya'll can try to assist me or refer me to another brother:

    Cell A1 contains a paragraph made of Hebrew words (They are Bible verses in Hebrew, so sometimes more than 254 characters long in each cell).

    Cell B1 contains a paragraph of English words (They are Bible verses in English, so sometimes more than 254 characters long in each cell).

    Column C contains a list of single Hebrew words.

    Column D contains the Strong's code for each of those Hebrew words.

    Column E contains a list of single English words.

    Column F contains the Strong's code for each of those English words.

    Finally, Column G contains a list of single words that will be replacing the English words with IF all criteria is met.

    (these words are transliterated single words).

    So, this is what I'm trying to do:

    IF a Hebrew word within the paragraph of cell A1, is the same as a Hebrew word found in the list in Column C,

    AND an English word within the paragraph of cell B1, is the same as an English word found in the list in Column E.

    AND the Strong's code for this word is the same in (both) Column D and Column F,

    THEN create a new paragraph (Bible verse) in a (new) Column H, which will be a combination: Rendering the existing English words from B1 that did not meet this criteria, but rendering the transliterated words from Column G when all criteria is met.

    I'm not sure Excel will support this task because of the 254 character issue, but since I don't know VBA, I figure getting help in Excel will be easier.

    Thanks for trying to help and especially for helping me pray for the solution to this.

    Praying to the Father: The love of JESUS help us walk in Your love, Amen.

  2. marty

    marty Beta Tester

    I haven't done anything with Excel in many years, but I used to dabble in it quite a bit. Here is my thought - and I could easily be wrong (the program has changed a lot and it's been YEARS since I worked with it): I agree that it's probably a stretch that Excel can support this task. I'm worried that there is too much data to sift in A1 and B1. In my experience Excel will use the entire content of a cell; it won't choose a portion. Data being data, if I had a few numbers in a cell, for instance, 92837439, I wouldn't expect Excel to go in and pick the 4 out of it and do something with it. And what if the prize number was the 9?

    It sounds like an interesting project. I wish you success - and I'd like to hear if you can make it happen! :)
    alexenposadas likes this.
  3. alexenposadas

    alexenposadas Member

    What a beautiful reply Marty! :) Refreshing and Spirit led. I recognize you from another forum and I confess I did wonder if you were walking with Abba when I saw your posts, because it certainly seemed so. :) Shalom
    marty likes this.
  4. marty

    marty Beta Tester

    I'm not on many forums, so it's a wonder you saw me other than here.

    Do you use SwordSearcher? It has wonderful extensive search features and uses Strongs - it will put the results in a list for you that you can copy and put into other programs... It's so easy to use. All the work is done for you. Seems like it would take care of what you want, no problem. If you don't have it, check it out.
    alexenposadas likes this.
  5. alexenposadas

    alexenposadas Member

    I was looking for you on because that's where I remember you from. If I remember well, you are a very active contributor when it comes to Microsoft Word. But I can't figure out where to search for members to find you there. Anyways, I was going to insist in getting help at that forum, where I posted this same question in the Excel 2010 forum section. But since we're here (less pressure from the world) let me be more specific with what I want to do, and perhaps you can guide me in the right direction (I'll be of course praying in tandem). Marty, I'm creating my own "Bible Unversion". I'll explain... hhahahah don't worry, I think you'll appreciate my project. I have a list of Hebrew words along with their Hebrew Strong's numbers (H1, H2, etc, etc) that I want to put back into the KJV text. Quick example, so you can see what I'm saying.. My KJV reads: "In the beginning God created the heaven and the earth"... and I want to turn it into... "In the reshit Elohim created the shamayim and the aretz". In other words, I will not create a totally transliterated Bible. It will be a "partially" transliterated Bible, so that "some" of the KJV words will be restored back to the original Hebrew (transliterated) word. Do you see what I want? So I have gathered all the data I will need in order to accomplish this task. You can imagine... it's a lot data! But that's okay, I got it all lined up nicely in excel files, I also have the Bible texts I will need and everything. I have also built in my mind what the Excel formula or VBA macro would have to do in order to accomplish it. My only problem is I know very little Excel and practically no VBA knowledge. Big problem huh :( But Abba is greater than any problem and I trust He is delighted to see me so interested in His Word and so excited about understanding His Word in Hebrew as He inspired it. Let me begin by asking you this question: Where would you start if you wanted to do this yourself? How would you get help, if you don't even know what a "VBA function" is, and don't know how to construct code that'll do what's in your mind to do? One answer would be, "Go to Computer school, become a programmer and then do it". But I can't do that, Marty. I live in Argentina, in a small town near the rain forest (border with Brazil and Paraguay), I have children and a family to support, and just to put you in perspective, my living conditions are not what I would call poverty, but for example, we don't have a kitchen, or fridge, or stove, and we didn't have the luxury of a hot shower until 2 months ago, when we we able to get a small water heater that's made for showers, which we got from Paraguay because here they're way too expensive. So, I'm not complaining at all :) Abba loves me and that's all I'm interested in. Walking with Him and copying Yeshua's behavior is what I'm into in life, and that's what I teach my little ones since they were born. I look at the good things Abba provides, for example an internet connection! Amen. Attached, I'm uploading a sample image (xls extensions and not allowed) so you can see how I have it all laid out. Shalom and blessings :)

    Attached Files:

  6. alexenposadas

    alexenposadas Member

    oops! all that I wrote, but I forgot to answer your question... hahaha. No, I'm downloading Sword Searcher and will have a look to see if it can help me do what I'm attempting. The thing is, I will nevertheless need to do find/compare/replace tasks. In my mind, there are IF statements and so forth, so there's programming involved.
  7. alexenposadas

    alexenposadas Member

    But one thing I just thought about is, perhaps a Microsoft Word macro can do the job. I actually have a Microsoft Word macro that I've managed to make it do a good part of the job, the problem is I don't know how to get it to fetch data from one file on one hand, then get data from another file on the other hand, then compare it all, and finally render the combination explained in the first post of my thread. But if you feel we can get help adapting this Microsoft Word macro I'm telling you about, then it might be worth the try.
  8. TerriP

    TerriP Active Member

    Amen, Alex, and Shalom to you as well!

    I was wondering... I work with Excel extensively and Marty's reply is spot on (as far as I have learned) and well explained, too (Marty are you a school teacher by any chance?! Because you are really good at this sort of thing...) I am wondering, though, if you could try Microsoft Office Access? Marty? Would he still have to separate out all those 254 words he was talking about? I have only dabbled in Access, just no time to get into it for now, but could that be a solution?
    alexenposadas likes this.
  9. marty

    marty Beta Tester

    Alex, I'm not the person you're looking for from the Microsoft forum. I don't post there. Sorry. Part of what you want to do is easy in SwordSearcher - you can get a list of all verses that contain a specific Hebrew or Greek word, using Strong's Links. I'll attach a screenshot from SwordSearcher - there are three panels in my layout: the first panel is the Bible panel (I've got the Compare feature toggled so I can see the KJV and KJVSL side-by-side); the second panel is the Search Results panel (I've done a search for reshit or reshiyth from the third panel - see the arrow); the third panel is from Strong's Links, which I got by clicking on the Hebrew word for 'beginning' (I marked it with a circle in an image program). There is a bunch of information there, and more under the Result Map link at the top of the Search Panel. As you look at the verses, you can see that they're what you're wanting to do - and the work is done for you. I would explore what you can do with SwordSearcher before you invest a lot of time in learning another program. Any time you rest the cursor on a linked word, a popup box will come up with a preview of what you get when you click the word. I hope this gets you excited about SwordSearcher's capabilities. :)

    Attached Files:

    alexenposadas likes this.
  10. alexenposadas

    alexenposadas Member

    Amen and Amen!
    Terri, MS Access is probably a good option, but as you said, there's a lot of time involved in understanding how to use it. Exceeding the 254, are not words, but characters, which I was concerned about, thinking Excel would not like more than 254 characters in one cell, but I've seen that it's not always a problem. Not sure yet though. Nevertheless, according to several people I've asked, my solution will be better accomplished with a VBA-Excel macro or perhaps a VBA-Word macro. As you can see, I'm all over the place :( until I actually consult with someone who knows VBA macros well enough to guide me. (So please keep me in prayer to find a brother or sister who can assist me with VBA). I'll certainly keep you and Marty informed of my progress because I can tell ya'll care and are interested in helping me :) I'll explain more thoroughly why I can't make use of SwordSearcher for this specific task in my next reply to Marty. Shalom in The Name above every name: JESUS
  11. alexenposadas

    alexenposadas Member

    Shalom Marty, ooooh I was sure it was you! Oh well. Here's why I can't make use of SwordSearcher for this specific task (But I really liked it and will be using it a lot for other things).
    The task involves taking OT words (along with their respective Hebrew Strong's codes) and assigning them to NT words (which normally have Greek Strong's codes tagged along to them). So neither SwordSearcher nor any other Bible software, for that matter, will have this done for me because it's never been done. I'm actually creating, if you will, a Hebrew Strong's concordance of the NT !!! I bet now you can see why my task is a bit more complex than it sounds... hhahahah
    So yeah, it will definitely take a VBA expert brother or sister to guide me in this journey. I know I'm close to the solution and I know Abba is pleased with my project so I'm gonna keep insisting in prayer until I get it done! Please keep me in prayer and if you can think of someone who knows VBA, praise Abba!
  12. Ray Luff

    Ray Luff Member

    I am a VBA expert retired one. But I need to let you know that this will not result in a good result. You need to understand symantic domains and the fact that in Greek and Hebrew there is more than one meaning for a word just as in English and that the context of the verse dictates the correct translation. Your tool would only provide garbage output for you because it does not include an understanding of the context of the sentence from which the words are plucked. Your best bet to understand the language is to enroll in both Hebrew and Greek training at your local Bible college. As far as I can tell translation is a human effort under discipline of knowing what you are doing and the care to the exercise that Our Lord can guide you in. A program will not do it. It is within the realm of possibility using a much more advanced artificial intelligent software engine but who could trust such an engine as it would only be as good as the one that programmed the intelligence into it.

    If you are doing this just to create a strongs index, I recommend you use the Hebrew indexing that already exists.

    God Bless.